lookup and worksheet name and then do formula

[LRM]

I have multiple worksheets named after customers...say "BW", "CAR", etc.
In the first summary worksheet I want to input formulas to grab info from
the correct worksheet. In other words, I want the formula in A2 to search
for a name in cell A1 that corresponds with the worksheet and then pull info
from a specific cell in that worksheet.

 

[Teethless mama]

A1 contain worksheet name
C2 is a info in that worksheet you want to pull

=INDIRECT(A1&"!C2")

 

[L. Howard Kittle]

Hi LRM,

I got this from Peo Sjoblom in 2006. It looks across eight worksheets, more
if needed. Even with a two page e-mail from Peo explaining it, I cannot
completely get my head around it. I grasp the concept and can apply it but
I do not fully understand it. Both formulas do the same thing, the first
combines all the sheet name into a named range and vastly shortens the
formula. The first is array entered (CTRL+SHIFT+ENTER) the second is by
ENTER.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

I would be glad to take a look at a sample workbook and see if I could make
it work for you if you cannot adapt it to your workbook.

HTH
Regards,
Howard