look up value on tab name?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jatman - 29 May 2008 19:37 GMT

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,

Reply to this Message

dhstein - 29 May 2008 19:57 GMT

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,
>
[quoted text clipped - 9 lines]
>
> thank you,

Reply to this Message

jatman - 29 May 2008 21:56 GMT

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
[quoted text clipped - 15 lines]
> >
> > thank you,

Reply to this Message

Mike H - 29 May 2008 20:00 GMT

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,
>
[quoted text clipped - 9 lines]
>
> thank you,

Reply to this Message

jatman - 29 May 2008 22:54 GMT

sorry, i reposted with a better example...

jat

> Hi,
>
[quoted text clipped - 21 lines]
> >
> > thank you,

Reply to this Message