LOOKUP to various different worksheets

[David]

Hi,

I am having a abit of problem with vlookup linking to various
different worksheets within a different workbook.

I have 2 workbooks, lets say workbook A and workbook B. In workbook A
there is about 50 worksheets with different names without pattern.
Workbook B is to be linked to workbook A to obtain various data, and
this can be done with vlookup or index/match.

My problem is that the 50 worksheets in workbook A have different
names and so I need to input the vlookup formula about 50 times (as
each lookup refers to a different worksheet in workbook A). At the
moment I simple copy the formula 50 times in workbook B and then use
REPLACE to change the reference in the formula to different worksheets
in workbook A. However this can be time consuming and tedious
especially when there is more than one set of these workbooks.

I can obtain a list of the worksheet names in Workbook A and put them
in worksheet B. But is there a way i can put it in to the formula??

Say the formula is,
=vlookup(A5,[Workbook A.xls]Sheet1'!$A1:B100,2,false)

Instead of putting "Sheet1" am I able to somehow put a cell reference
in there?

Thank you for reading, and all suggestions are welcome.

Regards,
David

 

[L. Howard Kittle]

Hi David,

Maybe something like this where I combined an INDIRECT solution I found to
another poster and added the lookup to it.

=VLOOKUP(C1,INDIRECT("'"&B3&"'!A1:B5"),2,0)

In C1 enter the value to lookup.
In B1 enter "Sheet" (No quotes).
In B2 enter sheet number, 2 or 3 or 16 etc.

Now all you have to do is change the sheet number in B2 to select the
various sheets.

HTH
Regards,
Howard

 

[Harlan Grove]

...
...

I can obtain a list of the worksheet names in Workbook A and put them
in worksheet B. But is there a way i can put it in to the formula??

Say the formula is,
=vlookup(A5,[Workbook A.xls]Sheet1'!$A1:B100,2,false)

Instead of putting "Sheet1" am I able to somehow put a cell reference
in there?

If Workbook A were open, you could use

=VLOOKUP(A5,INDIRECT("'[Workbook A.xls]"&X99&"'!A1:B100"),2,0)

If Workbook A could be closed, you're going to have to consider the alternatives
described in this linked article.

http://www.google.com/[email protected]