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

Rob,

One way is to create the referencing formulas with formulas, and then using
a macro to convert to actual formulas. For example:

In cell A2, put the folder path:
C:\Excel\Folder\

In cell B2, put a filename
Blah blah.xls

In cell C2, put the sheetname:
Sheet2

In cell D2, put the cell address:
A2

in Cell E2, put the formula
="='"&A2 & "[" & B2 & "]" &C2 & "'!" &D2

You can do this for as many cell addresses as you need, or sheets, etc. with
other columns, with one formula for each "link".

Then copy those cells down as far as you need, and properly increment the
filenames in column B. You
can use a formula to do so, if they are regular enough - say, based on a
date. Otherwise, you could use a macro to dump the names.

Then select the cells in column E, and run this macro:

Sub ConvStringToFormula()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = myCell.Text
Next
End Sub

HTH,
Bernie
MS Excel MVP


"Rob Welsh (Austin)" <Rob Welsh wrote in
message ...
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

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

"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

"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

"Bernie Deitrick" wrote:

Rob,

One way is to create the referencing formulas with formulas, and then using
a macro to convert to actual formulas. For example:

In cell A2, put the folder path:
C:\Excel\Folder\

In cell B2, put a filename
Blah blah.xls

In cell C2, put the sheetname:
Sheet2

In cell D2, put the cell address:
A2

in Cell E2, put the formula
="='"&A2 & "[" & B2 & "]" &C2 & "'!" &D2

You can do this for as many cell addresses as you need, or sheets, etc. with
other columns, with one formula for each "link".

Then copy those cells down as far as you need, and properly increment the
filenames in column B. You
can use a formula to do so, if they are regular enough - say, based on a
date. Otherwise, you could use a macro to dump the names.

Then select the cells in column E, and run this macro:

Sub ConvStringToFormula()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = myCell.Text
Next
End Sub

HTH,
Bernie
MS Excel MVP


"Rob Welsh (Austin)" <Rob Welsh wrote in
message ...
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