Discussion Groups Home

how do i use multiple cells to create a formula ? in Excel General Questions

11/30/2006 12:05 PM PST

Question

	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

Was this post helpful to you?

Top

Bernie Deitrick

11/30/2006 1:41 PM PST

Answer

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 (Austin)@discussions.microsoft.com> wrote in
message news:BF80D0ED-4656-4D1E-BFD2-338D8EB5ECE1@microsoft.com...
>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

Was this post helpful to you?

Top

Dave Peterson

11/30/2006 1:43 PM PST

Answer

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

Was this post helpful to you?

Top

	Return to Microsoft Communities		Notify me of replies