The indirect function and linking to other files
Posted by Harvey Lim on July 06, 1999 7:02 PM
This is my problem. I have 2 files: one file is called CODADOWN.XLS and it
resides on the following path: g:\finance\monthend\CODADOWN.XLS and it
contains a data range called CODADL.
I have another file called FILEONE which has the following vlookup
in b3 cell I have the following: CODADOWN.XLS!CODADL. As you know, the
indirect function only works if the other file (ie. CODADOWN.XLS) is already
open. But I want my vlookup to function to work even if CODADOWN.XLS is
closed and the only way is to have the function syntax as
I have to believe there is a better and shorter way to put the path in the
vlookup formula. I have this cell in many cells in the spreadsheet. If I
change where the CODADOWN.XLS file resides, I have to go through a massive
exercise of changing all the cells and the path with the search and replace
function. With the indirect function I only had to change the file path in
cell b3 and be done with it.
Is there a more elegant way to make the vlookup work if the datafile
CODADOWN.XLS is closed. The indirect function seemed like the best way,
but does anybody know another way ?