Returning a value for an actual cell address

Question

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?

Answer 1

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)

Answer 2

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).