The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Another option would be to a little work.

Use formulas to build the string that looks like a formula.

My "real" formula would look like:

='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1

My formula to build the string that looks like a formula may look like:

="='" & a1 & "\[" & b1 & "]" & c1 & "'!$A$1"

Depending on what's in A1:C1

Then I could drag that formula down the column.
Copy|paste special|values
Now it's just a string that looks like:
='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1
But it's not a formula (yet).

The last step is to select that range and
Edit|Replace
what: = (equal sign)
with: =
replace all

But do this last portion on just a small subset of your range. If there's a
mistake, you'll be dismissing dialogs forever if you use too large of a range.






Then convert those strings to values and

Rob Welsh (Austin) wrote:

I am linking many different cells from over 100 seperate files. As the cells
appear in the same location on each file I would like to know if I can join
text in serveral cells to create an external link rather than going into
every file and manually link each cell ?

Thanks Rob

--

Dave Peterson