How can I use and indexed value as the name of an array for vlooku

[JimR]

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\BadDog2007Monthly.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.

 

[Peo Sjoblom]

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

 

[David Biddulph]

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)