Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I use and indexed value as the name of an array for vlooku
I have a specific date in cell N1 field: 09/16/2007
I am taking only the month to generate the name of an Array called "SEPTAB". SEPTAB is the name of an array in a different worksheet. I want to use the date for the vlookup to the spread sheet that has the "SEPTAB" Array. Here are my values. Cell $n$1 contains 09/16/2007 Array ChuckTAB has: =IF((LEFT(N1,2)="09"),"SEPTAB","09") which returns "SEPTAB" properly and this statement is on line 9 of the ChuckTab array. I am then using it like: =VLOOKUP($N$1,'G:\mysubvol\Accounting\BadDog2007Mo nthly.xls'!INDEX(Chuckindex,(LEFT($N$1,2))),12) It should build this formula when the values are expanded =VLOOKUP(09/16/2007,'G:\mysubvol\Accounting\BadDog2007Monthly.xls '!SEPTAB,12) I hope I am just missing something subtle. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I use and indexed value as the name of an array for vlooku
It's not possible unless you use an add-in or have all workbooks open at the
same time (then there is no need for the path). In case you have the workbooks open you would need INDIRECT to "convert" your text string to the sheet name. If not you can use an add-in called Morefunc and a function called INDIRECT.EXT or you can do a Google search on pull Harlan Grove. There has never been a way to get the sheet name the way you tried -- Regards, Peo Sjoblom "JimR" wrote in message ... I have a specific date in cell N1 field: 09/16/2007 I am taking only the month to generate the name of an Array called "SEPTAB". SEPTAB is the name of an array in a different worksheet. I want to use the date for the vlookup to the spread sheet that has the "SEPTAB" Array. Here are my values. Cell $n$1 contains 09/16/2007 Array ChuckTAB has: =IF((LEFT(N1,2)="09"),"SEPTAB","09") which returns "SEPTAB" properly and this statement is on line 9 of the ChuckTab array. I am then using it like: =VLOOKUP($N$1,'G:\mysubvol\Accounting\BadDog2007Mo nthly.xls'!INDEX(Chuckindex,(LEFT($N$1,2))),12) It should build this formula when the values are expanded =VLOOKUP(09/16/2007,'G:\mysubvol\Accounting\BadDog2007Monthly.xls '!SEPTAB,12) I hope I am just missing something subtle. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I use and indexed value as the name of an array for vlooku
Note that =IF((LEFT(N1,2)="09"),"SEPTAB","09") will work if your input
09/16/2007 is a text string but not if N1 is an Excel date of September 16th formatted that way. Note also that the output "09" is a text string, not a number 9. If you want to return 9 from an Excel September date, use =MONTH(N1) -- David Biddulph "JimR" wrote in message ... I have a specific date in cell N1 field: 09/16/2007 I am taking only the month to generate the name of an Array called "SEPTAB". SEPTAB is the name of an array in a different worksheet. I want to use the date for the vlookup to the spread sheet that has the "SEPTAB" Array. Here are my values. Cell $n$1 contains 09/16/2007 Array ChuckTAB has: =IF((LEFT(N1,2)="09"),"SEPTAB","09") which returns "SEPTAB" properly and this statement is on line 9 of the ChuckTab array. I am then using it like: =VLOOKUP($N$1,'G:\mysubvol\Accounting\BadDog2007Mo nthly.xls'!INDEX(Chuckindex,(LEFT($N$1,2))),12) It should build this formula when the values are expanded =VLOOKUP(09/16/2007,'G:\mysubvol\Accounting\BadDog2007Monthly.xls '!SEPTAB,12) I hope I am just missing something subtle. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create and indexed field | Excel Discussion (Misc queries) | |||
format indexed cell | Excel Discussion (Misc queries) | |||
calculate percentile for indexed numbers. Thanks | New Users to Excel | |||
Sum from an indexed array/table | Excel Discussion (Misc queries) | |||
indexed line chart? | Charts and Charting in Excel |