lookup and worksheet name and then do formula
 

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.

lookup and worksheet name and then do formula
 

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

=INDIRECT(A1&"!C2")


"LRM" wrote:

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.

lookup and worksheet name and then do formula
 

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";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2: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


"LRM" wrote in message
...
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.