Cell reference question

[erichmiller]

I checked the archives but couldn't seem to find my answer. If anyone
knows or can point me in the right direction, I would appreciate it.

What I want to do is have a formula refer to a cell by retrieving the
row# from another cell's data. For example, cell D5 contains the number
3 in it. I have a formula in A1 that refers to column B and the row is
whatever value is in D5 - (i.e. B3.) How do you concatenate(?) the B
column with the row reference? I hope I made it clear enough.

Thank you,
Erich

 

[Peo Sjoblom]

Try something like


=INDIRECT("B"&D5)

with 3 in D5 it would return contents of B3
it can also be applied to offset

=OFFSET($B$1,D5-1,)

would yield the same result

you could even use the column header in another cell
with B in D4 and 3 in D5

=INDIRECT(D4&D5)

 

[dvt]

What I want to do is have a formula refer to a cell by retrieving the
row# from another cell's data. For example, cell D5 contains the
number 3 in it. I have a formula in A1 that refers to column B and
the row is whatever value is in D5 - (i.e. B3.) How do you
concatenate(?) the B column with the row reference? I hope I made it
clear enough.

Try something like =indirect("B"&D5).

Dave
dvt at psu dot edu

 

[erichmiller]

Thank you both for your help! I knew it was a simple function but
couldn't remember for the life of me! Thanks again.

Erich