Can I cell ref to ID a tab name in a formula?

Microsoft Office excel 2003
March 16, 2011 at 16:50:22
Specs: Windows XP, 3.4GH/2.5
How can I use a Cell reference to indicate to a VLOOKUP formula which Tab the array data is located on. I want to be able to control the tab the vlookup is using by indicating the tab name in a specified cell. Here's my attempt that failed.

=VLOOKUP($A2,INDIRECT($G$1)!$A$1:$B$100,2,FALSE)

This would allow me to specify the name of the tab in cell G1. E.g. Tab1, Tab2.

Thanks in advance!


See More: Can I cell ref to ID a tab name in a formula?

Report •

#1
March 16, 2011 at 17:44:08
I do not know if this will work at all, but shouldn't the syntax be something like:

=VLOOKUP($A2,INDIRECT($G$1)&"!"&$A$1:$B$100,2,FALSE)

MIKE

http://www.skeptic.com/


Report •

#2
March 17, 2011 at 10:13:07
Hi Mike, Thanks for trying. The formula you sent back causes a circular reference for the array. for some reason Excel formula assumes the array is on the page where the formula is rather than the one being noted in the referential cell G1. Any other ideas?
Thanks,

Report •

#3
March 17, 2011 at 11:55:27
Use the =INDIRECT() function to build the whole thing.

In Cell G1 put your tab name = Sheet2
In Cell H1 put your beginning range number = A1
In Cell I1 put in your ending range number = B100

Then build your formula:

=VLOOKUP($A2,INDIRECT(G1&"!"&$H1&":"&$I$1),2,FALSE)

See if that works for you.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 17, 2011 at 14:31:34
You-da-man! Works great. The power of this formula is that it will allow me to create dynamic VLOOKUPS. So if you have multiple tabs that you need to aggregate data from on to a single sheet, this formula can facilitate that functionality & permit you to do dynamic charts as well. Much appreciated Mike! If you're ever in LA, lunch is on me.
& the best part is I now have a better idea of the power of the INDIRECT function.

Report •

#5
March 17, 2011 at 14:46:08
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

Ask Question