Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and a function field input
Hey!
I am using a function to determine the name of the worksheet and place it into a cell: =MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,6) I then want to use the output of this cell [the first one is 1001] to feed into a vlookup: =VLOOKUP($K$2,List!$I$2:$K$579,2,FALSE) This works when I type 1001 into cell K2 but does not work when I use the first function to generate 1001 in cell K2. Both are formatted as number|general. I've tried several things - and the only thing that works in the vlookup function is if I physically type the sheet name [a number] into cell K2. Help? I must have missed something obvious. Thank you! Patrick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and a function field input
1. That function is not correct, you would need to specify any cell
reference or it will return wrong sheet name. 2. The result is text so you need to force it to be a number if the table has a numeric value =--MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,6) should suffice -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Patrick" wrote in message ... Hey! I am using a function to determine the name of the worksheet and place it into a cell: =MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,6) I then want to use the output of this cell [the first one is 1001] to feed into a vlookup: =VLOOKUP($K$2,List!$I$2:$K$579,2,FALSE) This works when I type 1001 into cell K2 but does not work when I use the first function to generate 1001 in cell K2. Both are formatted as number|general. I've tried several things - and the only thing that works in the vlookup function is if I physically type the sheet name [a number] into cell K2. Help? I must have missed something obvious. Thank you! Patrick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and a function field input
Thank you!!
The function works without a cell reference, but I understand the why it "should" be there. As for producing a number that vlookup can use - your solution works perfectly! Thank you! "Peo Sjoblom" wrote: 1. That function is not correct, you would need to specify any cell reference or it will return wrong sheet name. 2. The result is text so you need to force it to be a number if the table has a numeric value =--MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,6) should suffice -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Patrick" wrote in message ... Hey! I am using a function to determine the name of the worksheet and place it into a cell: =MID(CELL("filename"),SEARCH("]",CELL("filename"))+1,6) I then want to use the output of this cell [the first one is 1001] to feed into a vlookup: =VLOOKUP($K$2,List!$I$2:$K$579,2,FALSE) This works when I type 1001 into cell K2 but does not work when I use the first function to generate 1001 in cell K2. Both are formatted as number|general. I've tried several things - and the only thing that works in the vlookup function is if I physically type the sheet name [a number] into cell K2. Help? I must have missed something obvious. Thank you! Patrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup not recognizing field | Excel Worksheet Functions | |||
VLOOKUP Function | Excel Discussion (Misc queries) | |||
How do I access data stored in a SQL server for vlookup function? | Excel Worksheet Functions | |||
VLOOKUP Function using Data Ranges. | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions |