Using Sheet names in formulas
 

I need to be able to make my vlookup formula dynamically change th
range based on the result of another lookup....

Here is a simple vlookup:

VLOOKUP($a$1,L1!$a:$Z,2,0)

I need it to do something more like this

VLOOKUP($a$1,"$B1"!$a:$Z,2,0)

Using a cell ref in the table name would allow the value in $B1 tel
the formula what ws to look at

In this scenario A1 could be found on various sheets, and I need to b
able to figure out which one then look at that sheet.

It would be nice if I could do this with a formula trick instead of VB
since everyone here is scared of macro's......

--
Message posted from http://www.ExcelForum.com

Using Sheet names in formulas
 

Have a look in HELP index for INDIRECT.

--
Don Guillett
SalesAid Software

"bsullins " wrote in message
...
I need to be able to make my vlookup formula dynamically change the
range based on the result of another lookup....

Here is a simple vlookup:

VLOOKUP($a$1,L1!$a:$Z,2,0)

I need it to do something more like this

VLOOKUP($a$1,"$B1"!$a:$Z,2,0)

Using a cell ref in the table name would allow the value in $B1 tell
the formula what ws to look at

In this scenario A1 could be found on various sheets, and I need to be
able to figure out which one then look at that sheet.

It would be nice if I could do this with a formula trick instead of VBA
since everyone here is scared of macro's.......


---
Message posted from http://www.ExcelForum.com/

Using Sheet names in formulas
 

Works great, here's the end result

VLOOKUP(VLOOKUP(emplnk,EmplDB!$A:$Z,2,0),INDIRECT( VLOOKUP(emplnk,EmplDB!$A:$Z,9,0)),MATCH(A9,INDIREC T(VLOOKUP(emplnk,EmplDB!$A:$Z,10,0)),0),0)

VLOOKUP(emplnk,EmplDB!$A:$Z,2,0) = Employee ID
VLOOKUP(emplnk,EmplDB!$A:$Z,9,0) = Named Range
VLOOKUP(emplnk,EmplDB!$A:$Z,10,0) = Named Range

Now depending on what 'level' the agent is will tell the formula wha
ws to lookup from

--
Message posted from http://www.ExcelForum.com

Using Sheet names in formulas
 

glad it helped


--
Don Guillett
SalesAid Software

"bsullins " wrote in message
...
Works great, here's the end result


VLOOKUP(VLOOKUP(emplnk,EmplDB!$A:$Z,2,0),INDIRECT( VLOOKUP(emplnk,EmplDB!$A:$
Z,9,0)),MATCH(A9,INDIRECT(VLOOKUP(emplnk,EmplDB!$A :$Z,10,0)),0),0)

VLOOKUP(emplnk,EmplDB!$A:$Z,2,0) = Employee ID
VLOOKUP(emplnk,EmplDB!$A:$Z,9,0) = Named Range
VLOOKUP(emplnk,EmplDB!$A:$Z,10,0) = Named Range

Now depending on what 'level' the agent is will tell the formula what
ws to lookup from.


---
Message posted from http://www.ExcelForum.com/