How do link to a remote field but use the path from a stored 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!

First, the function you'd want to use is =indirect(), but it doesn't work with
closed workbooks.

(as an aside: If you create a couple of test workbooks, create a formula that
points at a cell in the 2nd workbook, then save and close the second, you'll see
the syntax for retrieving a value from a closed workbook.)

Harlan Grove wrote a UDF that allows you to retrieve values from a closed
workbook when you build that string that points at the
drive/folder/filename/sheetname/range address.

http://www.google.com/groups?selm=sH...wsranger. com

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Michael T. wrote:

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!

--

Dave Peterson