MrExcel Publishing
Your One Stop for Excel Tips & Solutions

The indirect function and linking to other files


Posted by Harvey Lim on July 06, 1999 7:02 PM


Hello all,

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

=if(iserr(vlookup("F8617",indirect(b3),3,false)),0,vlookup("F8617",indirect(
b3),3,false)

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

=if(iserr(vlookup("F8617",g:\finance\monthend\CODADOWN.XLS'!CODADL,3,false))
,0,vlookup("F8617",g:\finance\monthend\CODADOWN.XLS'!CODADL,3,false)

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 ?

Thanks


Posted by Ivan Moala on July 10, 1999 6:13 AM


Harvey,
Without actually trying it, you could try selecting
cell b3 and inserting a Named range Hyperlink
(make it relative so that if the file changes its
address it will change - do this by clicking Relative button)
to your file.
I should really try this myself......

Or you could set up a macro to search and replace
your new file address in the formulas, you will have to
set up an object for the formulas and loop through
each one exchanging the NewAddress for OldAddress...
if you are interested I could send you a routine.


regards


Ivan