View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default Automating references to tabs in formulas

In column B, try:

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

HTH
--
AP

"proper" a écrit dans
le message de ...

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.


--
proper
------------------------------------------------------------------------
proper's Profile:

http://www.excelforum.com/member.php...o&userid=33166
View this thread: http://www.excelforum.com/showthread...hreadid=529897