Vlookup using variable external filenames

  • Thread starter Thread starter Exceljr
  • Start date Start date
E

Exceljr

Hi, I'm trying to pull data from closed workbooks using VLookup.
have no trouble using a particular filename such as:

=VLOOKUP("Grand Total",'N:\Nurse\Archive\[20040223 Nurs
08AM.xls]NURSE'!R6C1:R25C5,5,FALSE)

But, everyday there is a new file with the current date in the nam
(the one above is from Feb 23 of 2004)
I'm trying to find a way to substitute the filename with a variable
something like:

DIM THEFILE AS STRING
THEFILE = N:\Nurse\Archive\[20040223 Nurse 08AM.xls

=VLOOKUP("Gran
Total",'N:\Nurse\Archive\[THEFILE]NURSE'!R6C1:R25C5,5,FALSE)

I'm not having any luck. Any ideas? Perhaps using VB code instead o
a worsheet function
 
Jr.,

Sorry about that last post - not very helpful , was it. <g>.

The easiest way to do this is to simply select all your formulas and simply
hit Ctrl-H, then replace 0223 with 0224, to change from the 23rd to the
24th.

Just make sure you change a string that is definitely only part of the file
name, and not part of a cell address: use all eight digits if that makes
sense.

HTH,
Bernie
MS Excel MVP
 
Hi
normally one would use INDIRECT to reference a range combined from
string. But INDIRECT does not work on closed workbooks!
There're some alternatives (see link below) But in your case also the
function INDIRECT.EXT won't do as it can't return a range from your
closed workbook. so see the remaining alternatives in:
http://tinyurl.com/2c62u
 
Back
Top