How do link to a remote worksheet using the path value in a field?
 

I want to link the fields on one worksheet to the values stored in several
other Excel files. (That part is easy.) But I want the ability to move the
underlying files to a new folder and be able to easily update all the links
in my worksheet.

One idea I had was to store the path in a specific field on the new
worksheet and use the value from that cell concatenated in as part of all the
link references. (Ex: store the path value in cell A1: \\[type UNC path
here]; then create a link that concatenates the value from A1 to the
filename, worksheet name, and cell reference info.) Then, if I move the
underlying files, I simply update the path value in my worksheet in the cell,
and all the link references automatically are updated. However, I have not
found the correct syntax for constructing a link reference using
concatenation. Will this idea work and, if so, what is the proper syntax, or
is there another better method for quickly updating multiple links when the
path of the source files is changed? Any help is greatly appreciated!!!
(P.S. The version is currently Excel97, but I suspect the solution will be
the same in later versions, as well.) Thanks!

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

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

Michael T. wrote:
The concatenation works fine, until I attempt to
autofill the formula down the rows or across the columns.

For the cell address part use e.g.
=INDIRECT(... & ADDRESS(ROW(B2),COLUMN(B2),4))
where B2 is the cell you want to link to.
This will fill OK.

The structure you end up with will not be the most efficient way of
getting a block of data from one sheet into another.

For example, you could name the entire range on the source sheet
"MyTable" and array-enter (Ctril+Shift+Enter) the =INDIRECT(...)
formula into the whole of the destination table using MyTable in place
of the source reference

Or you could consider using a database query to extract the information
from the source sheet (if it is suitable arranged)

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