Reputation: 111
I have two sheets: sheet1 and sheet2.
In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc, all referring to cell locations in Sheet1.
In sheet1 in column "B" are names.
I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.
Is there an Excel formula that will do this?
Upvotes: 0
Views: 179
Reputation: 38551
Yes. This is very the purpose of the INDIRECT
function.
In cell G2, you can write:
=INDIRECT("sheet1!"&C2)
and copy this formula down column G.
This will take the text in cell C2, say $J$740
, and append it to the text sheet1!
to give sheet1!$J$740
. The INDIRECT
function then returns the value of the cell corresponding to that address (cell J740 on sheet1).
Upvotes: 1
Reputation: 111
Hi all I found the answer to my question - thanks again for your help
=OFFSET(Sheet1!B$1,RIGHT(C2,LEN(C2)-FIND("$",C2,2))-1,0)
Upvotes: 0