Excel - Vlookup and links to another workbook

  • Thread starter Thread starter cbl1008
  • Start date Start date
C

cbl1008

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 an
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 int
the value in Column A and know which file to get info from?

Any help will be greatly appreicated.

Thanks,
Chuc
 
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
 
Thanks Frank. For formula works perfectly . but is there a way where
don't need to have the source files opened
 
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
 
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. . . .
Click to expand...

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/[email protected]
 
Harlan said:
... ..

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.
Click to expand...
http://www.google.com/[email protected]
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
 
Back
Top