Using tab name as lookup variable

[TRNELSON5]

I have a document with multiple tabs, all named with city tricodes. I would like to have another tab where I can input one of these tricodes to have other cells lookup data from that tab. IE, a1 could read "ATL" and b1 could read "4" and it could lookup row 4 form the ATL tab to fill in the rest row 1. I would use this for multiple rows with multiple tricodes.

Please let me know if anyone can help.

 

[T. Valko]

I have a document with multiple tabs, all named with city tricodes. I would like to have another tab where I can input one of these tricodes to have other cells lookup data from that tab. IE, a1 could read "ATL" and b1 could read "4" and it could lookup row 4 form the ATL tab to fill in the rest row 1. I would use this for multiple rows with multiple tricodes.

Please let me know if anyone can help.

Try something like this...

On some sheet:

• A2 = ATL
• B2 = 4

Enter this formula in C2 and copy across as needed:

=INDEX(INDIRECT("'"&$A2&"'!1:100"),$B2,COLUMNS($C2:C2))

I've defined the range on the ATL sheet to be rows 1 to 100. Adjust to suit.

 

[TRNELSON5]

I'm messing around with this, and I may be able to adjust this to work, but what I was really hoping for was something similar to a VLOOKUP function. The "column b" number would search for a value in row a of the "column a" table?

Can we adjust?

 

[T. Valko]

I'm messing around with this, and I may be able to adjust this to work, but what I was really hoping for was something similar to a VLOOKUP function. The "column b" number would search for a value in row a of the "column a" table?

Can we adjust?

What is the actual range address of the table?

If:

A2 = ATL (sheet name)
B2 = 4

What exactly does the 4 mean?

This doesn't make any sense to me:

search for a value in row a of the "column a" table?

 

[TRNELSON5]

The ATL Sheet is a roster set up like this.
a b c
1 # pos name
2 1 joe smith
3 2 john doe

I adjusted the formula you originally gave me so to reference a row down, so it works very well, but I could go through one less step of reassigning everything to a new row if we could lookup the number in column a.

 

[T. Valko]

The ATL Sheet is a roster set up like this.
a b c
1 # pos name
2 1 joe smith
3 2 john doe

I adjusted the formula you originally gave me so to reference a row down, so it works very well, but I could go through one less step of reassigning everything to a new row if we could lookup the number in column a.

Maybe this...

=VLOOKUP(B2,INDIRECT("'"&$A2&"'!A:D"),COLUMNS($C2:C2),0)

The table range evaluates to ATL!A:D.

 

[TRNELSON5]

Thats exactly it . .. thanks a lot!

 

[T. Valko]

Thats exactly it . .. thanks a lot!

You're welcome. Thanks for the feedback!