Using a function to select a cell

**dfinlay** · 02-14-2010, 05:33 PM

I was wondering if it was possible to specify a cell with a function. For example, if you put in a value of 1, it will give you the value of H1. If you input 2, it will give you H2, and so on.

**teylyn** · 02-14-2010, 05:45 PM

Hi,

welcome to the forum.

=INDIRECT("H"&A1)

where A1 is the number you specify

**Richard Buttrey** · 02-14-2010, 05:51 PM

Hi,

If I understand you correctly you are wanting to enter a constant and after entry have it change to a formula to evaluate the contents of H1. You can't do this.

You can put 1 in a cell (say E2) and then in another cell enter the formula
=INDIRECT("H"&E2)
which will result in the value in H1, H2 etc.

HTH

**rwgrietveld** · 02-14-2010, 06:41 PM

Both Teylyn and RB use Indirect, nothing wrong with that !

=INDEX(H1:H100,E1,1) will do the same, where E1 (again) is your constant

Another possibility is to use =OFFSET(H1,E1-1,0)

**dfinlay** · 02-16-2010, 02:54 AM

Thanks a lot. I'm sorry if I was unclear, but I did want the constant in another cell. Indirect is what I needed. How do I set this to solved now (or can I)?

**DonkeyOte** · 02-16-2010, 03:59 AM

FWIW, unless the sheet reference is itself variable I would suggest using Ricardo's INDEX rather than INDIRECT given the latter is Volatile and the former is not.
(OFFSET too is Volatile).