Worksheet Name

[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.

 

[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.

 

[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

 

[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.

 

[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