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.
--
Greg McLandsborough

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.
--
HTH,
Barb Reinhardt



"Greg" wrote:

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.
--
Greg McLandsborough

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


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

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.
--
HTH,
Barb Reinhardt



"Tyro" wrote:

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


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

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

"Barb Reinhardt" wrote in message
...
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.
--
HTH,
Barb Reinhardt



"Tyro" wrote:

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


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