vlookup using text to refer to other workbooks

[nepentanova]

ok i'll try and explain as clearly as possible
in column b week commencing dates, eg 05/10/03, 12/10/03
in column c are id's that are drawn from a cell that the user enters.
so i need to lookup the id and return the x column in a workbook that
is named with the week commencing eg C:\data\20031005.xls
is it possible using text and concatenate functions, or vb, to use
vlookup to look in the correct workbook dependent on the date in
column b.
I hope i've explained clearly.
I've been working on this for some time, and hopefully the knowledge
in this group could provide a solution.
TIA

 

[Dave Peterson]

I think the worksheet function you'd want to use is =indirect(). But the bad
news is =indirect() won't work with closed files. And I'm guessing you won't
open all the possible workbooks so that the one you want will be open.

But John Walkenbach has some code that will allow a macro (not a worksheet
function) retrieve values from a closed workbook.

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

But Harlan Grove posted a function that opens the other workbook and retrieves
the value from a separate instance of excel:
http://google.com/[email protected]