simple look up formula.....

j33pguy

Well-known Member
Joined
Aug 6, 2004
Messages
633
hi, i have the following formula.... =LEFT('5'!$D16,1) which would look at the tab named 5 and take a letter from the word in D16.
works fine.
now, what if in my current sheet, i have the tab name (ie 5) in a cell. how can i make this dynamic?
if 5 is shown in A1, can i do: =LEFT('"A1"'!$D16,1)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

j33pguy

Well-known Member
Joined
Aug 6, 2004
Messages
633
thanks so much....oh and you are so Ridiculously goodlooking!!!! ;)
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Really, really, ridiculously good looking. That's le tigre, can't you tell? ;)
 

j33pguy

Well-known Member
Joined
Aug 6, 2004
Messages
633
one thing though....
i need D16 to change...as in i need to scroll down that formula and have it change....
i tried =LEFT(Indirect('"&A1&"'!$D16),1) but it gives me a REF error.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
j33pguy said:
one thing though....
i need D16 to change...as in i need to scroll down that formula and have it change....
i tried =LEFT(Indirect('"&A1&"'!$D16),1) but it gives me a REF error.

=LEFT(INDIRECT("'"&$A$1&"'!"&CELL("Address",D16)))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,821
Messages
5,574,515
Members
412,599
Latest member
Schu94
Top