Bill, Thank you very much for your help! I was able to make the INDIRECT
command work. I do have one question for you.

In place of the A5 cell references below (for the Cell location), I want to
hard code this portion of the data in the reference because this data won't
change (unlike the A1 & A2 path portion or even the A3 filename). There are
several hundred different cell locations that would have to be stored in
individual "A5" fields. The concatenation works fine, until I attempt to
autofill the formula down the rows or across the columns. The hard-coded
cell location data is a string, so it does not increment during the autofill
but remains constant, and I have to manually change each cell location. Is
there a way to use the actual cell location in the reference (as opposed to
referencing a field A5 that contains the cell location) that will still
increment properly during the autofill? Any help is deeply appreciated!

"Bill Manville" wrote:

Michael T. wrote:
Will this idea work and, if so, what is the proper syntax,


To use a formula to create the file name to link to you need to use the
INDIRECT function, e.g.
=INDIRECT("'\\" & A1 & "\" & A2 & "[" & A3 & "]" & A4 & "'!" & A5)

But the INDIRECT function only works if the source file is open.

is there another better method for quickly updating multiple links when the
path of the source files is changed?

If you keep all the files in the same folder, or have the source files in
sub-folders of the folder containing the destination file then links will
automatically adjust (they are effectively held as relative links).

Otherwise, you would need to use Edit / Links / Change Source to change the
links.
In Excel 2002 or later you can change the startup behaviour for links which
makes it easier to write a macro to run on startup which checks for the
presence of the sources and requests a new folder if the old one is not
accessible, and then changes all the link sources for you.

I can provide a free LinkManager utility (email me at Bill_Manville @
compuserve . com ....without the spaces) which will change link sources in all
files in a directory structure, but it isn't really designed for just changing
the links in a single file.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup