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. |
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. |
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. |
All times are GMT +1. The time now is 09:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com