dynamic table_array in vlookup

Hi,

I'm trying to vlookup to a table in a different spreadsheet that has a different tab for every month, however I can't seem to concatenate the literal path and file name with the dynamic tab name. Assuming I have the date in A1 and the text to lookup in B1, I'd imagine I'd need something like

=vlookup(B1,'C:\path\[spreadsheet.xls]' & month(A1) & year(A1) & !$A:$B,2,false)

I've tried every way to concatenate I can think of within this cell and calling literals from other cells, and I'm totally stumped. Any help would be very very much appreciated! Thanks!!!!

Kate

Here is one solution that works for me:
=VLOOKUP(B1,INDIRECT("[Book2.xls]"&MONTH(A1)&YEAR(A1)&"!$B:$C"),2,0)

Hope it works for you
Ola

Hi Ola,

Thanks for the reply. The problem is that the other file isn't in the same folder, and the Indirect statement seems to not work once I add the path in front of the name. So

=VLOOKUP(B1,INDIRECT("[Book2.xls]"&MONTH(A1)&YEAR(A1)&"!$B:$C"),2,0)
works fine, but when I move Book2 to my C:\ drive and use

=VLOOKUP(B1,INDIRECT("C:\[Book2.xls]"&MONTH(A1)&YEAR(A1)&"!$B:$C"),2,0)

I get a #ref error. Thanks!!

Kate

I think this is what you are looking for.
The late Mr Frank Kabel explains in his post at the Daily Dose of Excel Blog, 3 way "How to link to cells in a closed Workbook".
It is a bit tricky but it works.

HTH
Ola Sandström

One of the links in his post is broken, so here's a renewed link:
Q: Can I use the INDIRECT function to reference a value in an unopened spreadsheet?
A: No, you can't use INDIRECT, but you can use SQL.REQUEST.

Thank you again for your help, Ola. Unfortunately (should have specified before, sorry), I don't have editing access to the spreadsheet that I'm calling values from to make a Table name, and since the tab changes every month, it wouldn't be ideal for me to ask the owner to create a new table every month. Thank you though!!

This might be a solution: ExcelCube

I just saw it, so I haven't tried it.
If if anyone does try it, please send a comment. It does sound simple and useful enough.
The freeware restriction is One sheet in ever workbook.

//Ola


I'm currently evaluating a few cube alternatives (Palo and PowerOLAP). If anyone has any other good suggestions for Cube freewares that reads/writes info to/from a database/cube. Please send a link.