Entering data which refers to different data in another cell

Hi All,
This may be an ubernoob question; and for that I apologize.....if possible kindly assist?

...on the other hand this query may need an Excel Pro to figure out....hmmm...

I am trying to prepare a salary worksheet for our staff here at school. I have a column labeled "Years Experience" which I need to actually refer to a tabulated salary scale which will go into a final formula for salary calculation.

In particular, I'm looking for a way to enter in a number (from 1 to 20 in this case) in a cell (under the "Yrs.Experience" heading) which in fact defines the cell with a different value taken from another cell in the worksheet (the salary data).

I need, for example when I enter in "13" (thats years of experience) into say, Cell O5 ("O" being the "Yrs.Experience" column), to then in fact define Cell O5 to another set of tabulated data which in this case is in Cell G55 (which happens to be 17500).

However I need the "13" to remain typed in Cell O5 as such (I dont want it to switch over to 17500).

So I type in "13" into O5 but the formula bar actually reads "17500" (or "G55"). From this other calculations are straightforward.

Please see attachment as a visual example..

cheers,
Robin

Originally Posted by DonkeyOte

Rather than trying to store both remuneration and year values within the same cell why not simply conduct a VLOOKUP in your final summation, eg:

=(H4*K8)+(I4*K9)+(J4*K10)+(K4*K11)+(K12*L4)+(M4*K13)+VLOOKUP(N4,B8:G27,6)

or

=SUM(SUMPRODUCT(H4:M4*TRANSPOSE(K8:K13)),VLOOKUP(N4,B8:G27,6))
confirmed with CTRL + SHIFT + ENTER

Thanks "Don" =)
This is a great fix....the only problem is that I have to put in a value greater than zero in cell N4 (yrs.experience) in order for the formula to work.

I need to be able for this salary calculator to allow the user to quickly enter in in the relevant information from a persons resume and the offer pops out. So, for example, if there is a new university grad with a bachelors degree and zero years of experience how can I enter in "0" in cell N4 and have the "Offer" cell (O4) read $30000?
cheers!
--Robin

Great! Thank you kindly! I just added to your 'reputation beyond repute" =)
cheers
--Robin