how do i use multiple cells to create a formula ?
"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
|