Worksheet Name
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 |
Worksheet Name
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 |
Worksheet Name
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 |
Worksheet Name
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 |
Worksheet Name
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 |
All times are GMT +1. The time now is 07:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com