look up value on tab name?

[jatman]

good afternoon,

i need to find a formulae to lookup something on a tab, where the tab name
can be different, based on various alternatives.

i have used the formulae
MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,32) before, but i
cannot find any information on how to use this.

is there a website where i can find information on referencing vlookup
commands on different tabs based on the value of a cell?

thank you,

 

[dhstein]

If you use that formula in a cell, you will get the name of your worksheet.
Assume that is in cell Z1, you can then reference that with something like
INDIRECT (Z1&"!Q1") This will return the value in cell Q1 of this
worksheet. You can build addresses like that to create ranges or whatever
you need. I'm not sure if that's your question.

 

[Mike H]

Hi,

You don't give too much infoormation but have a look at this formula and see
if it helps

=VLOOKUP(C1,INDIRECT(G1&"!A1:B13"),2,FALSE)

Your formula would be in G1 and would return the sheet name so this lookup
would do a vlookup on that sheet name.

Mike

 

[jatman]

better explanation of formula

this formulae looks for the item in A6 on the sheet SN in Column A, and
returns what is in column B (the easy one because i know the tab to look up
on)
=VLOOKUP(A6,SN!A:B,2,FALSE)

i need the tab SN to change into a VLOOKUP so that the tab to look on can
change based on another value. B2 is the name to look up on the supplier
tab, in column B. in Column C would be a short name and there would another
sheet with that name.
=VLOOKUP(B2,supplier!B:C,2,FALSE)

when i try to use an indirect lookup, i always get a circular reference and
the formula changes go ...))*A:B,...

=VLOOKUP(A7,INDIRECT(VLOOKUP(B2,supplier!B:C,2,FALSE))*A:B,2,FALSE)

any suggestions on the formula would be appreciated.

thank you,

If you use that formula in a cell, you will get the name of your worksheet.
Assume that is in cell Z1, you can then reference that with something like
INDIRECT (Z1&"!Q1") This will return the value in cell Q1 of this
worksheet. You can build addresses like that to create ranges or whatever
you need. I'm not sure if that's your question.

good afternoon,

i need to find a formulae to lookup something on a tab, where the tab name
can be different, based on various alternatives.

i have used the formulae
MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,32) before, but i
cannot find any information on how to use this.

is there a website where i can find information on referencing vlookup
commands on different tabs based on the value of a cell?

thank you,

 

[jatman]

sorry, i reposted with a better example...

jat

Hi,

You don't give too much infoormation but have a look at this formula and see
if it helps

=VLOOKUP(C1,INDIRECT(G1&"!A1:B13"),2,FALSE)

Your formula would be in G1 and would return the sheet name so this lookup
would do a vlookup on that sheet name.

Mike

good afternoon,

i need to find a formulae to lookup something on a tab, where the tab name
can be different, based on various alternatives.

i have used the formulae
MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,32) before, but i
cannot find any information on how to use this.

is there a website where i can find information on referencing vlookup
commands on different tabs based on the value of a cell?

thank you,