Assuming that B1 on Sheet11 contains your lookup value, and Sheet1
through Sheet10 contain your lookup tables, try...
=VLOOKUP(B1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDI
RECT("1:10"))&"!A2:A100"),B1)0,0)&"!A2:F100"),6,0 )
or
=VLOOKUP(B1,INDIRECT("'"&INDEX($A$1:$A$10,MATCH(TR UE,COUNTIF(INDIRECT("'"
&$A$1:$A$10&"'!A2:A100"),B1)0,0))&"'!A2:F100"),6, 0)
....where A1:A10 on Sheet11 contains your list of sheet names. Both
formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Adjust the ranges (A2:A100 and A2:F100) accordingly.
Hope this helps!
In article ,
"Laura" wrote:
I'm in over my head. Any help would be appreciated.
I have a workbook with 11 worksheets. I need to look for a value in column
A within the first 10 worksheets and return the corresponding value from
column F to worksheet number 11. Column A contains both text and numbers.
Column A Column F
Product Units
5 200
1 100
3 12
7 50
The numbers in column A are indexes from drop down boxes.
Please let me know if you need further info. Thank you for any help you can
give.
Laura
|