Worksheet Name

G

Greg

I am trying to use Vlookup where the table_array refers to data on another
worksheet. However I have three worksheets with different data in the same
format. I have a cell which returns the Worksheet name ie 'Grating', 'Steel'
or 'Bolts'. How do I use this in vlookup. I seem to keep getting an error. I
have tried Concatenating name and data array, but it seems not to evaluate
the string..

Any help would be greatly appreciated.
 
B

Barb Reinhardt

As I recall, you need to use indirect to do this. I think it's like this

=VLOOKUP(A1,indirect("'Sheet1!A2:B100").2,false)

If you concatenate information together, make sure you hae a single quote
before and after the sheet name.
 
T

Tyro

Assuming the worksheet name (Grating, Steel, Bolts) is in cell A1 and the
lookup array is A10:B15 on the other sheets and the lookup value is in A2
then:
=VLOOKUP(A2,INDIRECT(A1&"!A10:B15"),2,FALSE) will lookup A2 in the lookup
array and return the value from column B for an exact match in column A

Tyro
 
B

Barb Reinhardt

That will work if the worksheet name has no spaces. If it has spaces, you
must use 'Sheet Name'. I've had issues with the ' in the past though.
Sometimes it pulls something from A1 and sometimes it doesn't.
 
T

Tyro

The OP said his sheet names are Grating, Steel and Bolts. Those names have
no spaces and do not require single quotes around them. Thus I did not put
the single quotes in the formula.

Tyro
 
Top