Find Worksheet Based on Data in a Cell

In Sheet1 I've got a list of names in column A, then I've got a worksheet for each name in the list. I'd like to fill in column B with information from the worksheet of the same name as in column A.

Quick example...name in column A is Davis, want to fill in column B with a value from cell C3 on worksheet named Davis and do it for the entire list. Data being looked for is in same cell on each worksheet.

Tried using a formula and dragging it down the list, problem with that is it doesn't change the sheetname just the cell.

Thinking the solution is in VBA land...but hoping not.

TIA,

Jeff

Hi Jeff,

You can use the INDIRECT function for this, e.g.

=INDIRECT("'"&A1&"'!C3")

You have to change the reference of A1 to the cell reference with your sheet name in it. In your uploaded file, change the first formula from A1 to A4 so it references the cell that contains Jeff. Copy that formula down and it will reference the text in A5.