Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dragging formulas with sheet names | Excel Discussion (Misc queries) | |||
Using Sheet names in formulas | Excel Worksheet Functions | |||
Sheet names used in formulas | Excel Discussion (Misc queries) | |||
replacing just the sheet names within formulas | Excel Worksheet Functions | |||
Locking Sheet names in formulas | Excel Worksheet Functions |