Excel - Vlookup and links to another workbook

Hi All,


Source file - receive everyday and it named as 01.24.04abc.xls,
01.25.04abc.xls,01.26.04abc.xls and so on

Lookup File -

Date Lookup Value
01/24/04 =vlookup(a2,'path\01.24.04abc\tab\Range,4,0)
01/25/04 =vlookup(a3,'path\01.25.04abc\tab\Range,4,0)
01/26/04 =vlookup(a4,'path\01.26.04abc\tab\Range,4,0)

What I have been doing is going into the lookup up file and do find and
replace filename on cells where I have the vlookup formula everyday.

Is there a way to make the filename dynamic so that it's looking into
the value in Column A and know which file to get info from?

Any help will be greatly appreicated.

Thanks,
Chuck


---
Message posted from http://www.ExcelForum.com/

Hi Chuck
with the assumption that your source file is also opened you can use
the following
=vlookup(a2,INDIRECT("'path\" & MONTH(A2) & "." & DAY(A2) & "." &
RIGHT(YEAR(A2),2) & "abc\tab\Range"),4,0)
Note: INDIRECT won' work if the other file is closed.

HTH
Frank

Hi All,


Source file - receive everyday and it named as 01.24.04abc.xls,
01.25.04abc.xls,01.26.04abc.xls and so on

Lookup File -

Date Lookup Value
01/24/04 =vlookup(a2,'path\01.24.04abc\tab\Range,4,0)
01/25/04 =vlookup(a3,'path\01.25.04abc\tab\Range,4,0)
01/26/04 =vlookup(a4,'path\01.26.04abc\tab\Range,4,0)

What I have been doing is going into the lookup up file and do find
and replace filename on cells where I have the vlookup formula
everyday.

Is there a way to make the filename dynamic so that it's looking
into
the value in Column A and know which file to get info from?

Any help will be greatly appreicated.

Thanks,
Chuck


---
Message posted from http://www.ExcelForum.com/

Hi

unfortunately in your example: No. Though there is a free add-in to
overcome the restriction of INDIRECT for closed files (see MOREFUNC.XLL
available at http://longre.free.fr/english - function INDIRECT.EXT)
this won't work in your combination with VLOOKUP. One way around this:
1. Create a new (hidden) sheet in your main workbook on which you copy
the data from your source file using ONLY the function INDIRECT.EXT
2. Reference with VLOOKUP on this sheet

HTH
Frank

Thanks Frank. For formula works perfectly . but is there a way
where I don't need to have the source files opened?

Harlan Grove wrote:
"Frank Kabel" wrote...
unfortunately in your example: No. Though there is a free add-in to
overcome the restriction of INDIRECT for closed files (see
MOREFUNC.XLL available at http://longre.free.fr/english - function
INDIRECT.EXT) this won't work in your combination with VLOOKUP. . .
..

Correct with respect to INDIRECT.EXT - looks like it can return only
single values. Incorrect with respect to that being the only
alternative that wouldn't require additional formulas in ancillary
cells. See the third alternative in the following.


http://www.google.com/groups?selm=zK...40www.newsrang
er.com

Hi Harlan

sorry I forgot to mention this alternative though I tried it some time
ago. But (as you mentioned in your original post) this alternative is
very slow. Especially then referencing a large range in the closed
file. So the OP has to test for himself if this fits his needs. (Maybe
sometime Microsoft will extend INDIRECT...)

Regards
Frank