Automating references to tabs in formulas

[proper]

Not sure if that's possible, but here is the situation I have.

Say I use a formula on a summary tab that pulls numbers from data1,
data2, data3, etc. tabs. In this particular case, I am using VLOOKUP.
So, my formulas will look like this:

Column B---------------------------------Column C
=VLOOKUP(A1,DATA1!A:B,2,0)---------=VLOOKUP(A1,DATA2!A:B,2,0)
...... many rows go here with VLOOKUP

I have to manually adjust references to DATA tabs for each additional
column I add, so I will go and change data1, data2, etc. in VLOOKUP
each time a new column is added. Is there a way to enter DATA1, DATA2,
etc. above columns and then just reference them into the VLOOKUP
function. Like this:

Column B-----------------------------------Column C
DATA1 (-cell B1-)----------------------------DATA2 (-cell C1-)
=VLOOKUP(A1,Ref_to_B1!A:B,2,0)------=VLOOKUP(A1,Ref_To_C1!A:B,2,0)
...... many rows go here with VLOOKUP

I tried using something like _"&B1&"!A:B"_ for the second argument in
VLOOKUP, but it does not seem to work.

Any help is greatly appreciated.

 

[Biff]

Hi!

One way:

=VLOOKUP(A1,INDIRECT("'"&B1&"'!A:B"),2,0)

Biff

 

[Ardus Petus]

In column B, try:

=VLOOKUP(A1,INDIRECT('DATA'&COLUMN()-1&"!A:B");2;0)
then drag to the right

HTH

 

[proper]

It worked. Thanks guys

 

[Biff]

You're welcome. Thanks for the feedback!

Biff