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 or vb script that will do this?

I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.