spittingfire
spittingfire

Reputation: 111

Returning a value for an actual cell address

I have two sheets: sheet1 and sheet2.

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

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

Upvotes: 1

spittingfire
spittingfire

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

Related Questions