Find Worksheet Based on Data in a Cell

[collinjc]

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