software

I am looking for syntex that will build a formula.

I want to replace this reference
='[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!L19

with a formula that will build the reference using data from another part of
the worksheet. It would be something like this CONCATENATE(quot;='[2006 Capacity
PMHS Bourbonnais.xls]Capacity Synopsis'!quot;,F$2,$A9). where F$2 = L and $A9 =
19. Only this doesn't work.
You need the INDIRECT() function. Bear in mind it doesn't work when the
workbook you're referencing is closed. If the referenced workbook is open it
will work just fine.

=INDIRECT(quot;'[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!quot;amp;F$2amp;$A9)
quot;lynncquot; wrote:

gt; I am looking for syntex that will build a formula.
gt;
gt; I want to replace this reference
gt; ='[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!L19
gt;
gt; with a formula that will build the reference using data from another part of
gt; the worksheet. It would be something like this CONCATENATE(quot;='[2006 Capacity
gt; PMHS Bourbonnais.xls]Capacity Synopsis'!quot;,F$2,$A9). where F$2 = L and $A9 =
gt; 19. Only this doesn't work.
gt;
gt;

Is there a way to reference a closed file?

quot;Duke Careyquot; wrote:

gt; You need the INDIRECT() function. Bear in mind it doesn't work when the
gt; workbook you're referencing is closed. If the referenced workbook is open it
gt; will work just fine.
gt;
gt; =INDIRECT(quot;'[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!quot;amp;F$2amp;$A9)
gt;
gt;
gt;
gt; quot;lynncquot; wrote:
gt;
gt; gt; I am looking for syntex that will build a formula.
gt; gt;
gt; gt; I want to replace this reference
gt; gt; ='[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!L19
gt; gt;
gt; gt; with a formula that will build the reference using data from another part of
gt; gt; the worksheet. It would be something like this CONCATENATE(quot;='[2006 Capacity
gt; gt; PMHS Bourbonnais.xls]Capacity Synopsis'!quot;,F$2,$A9). where F$2 = L and $A9 =
gt; gt; 19. Only this doesn't work.
gt; gt;
gt; gt;

gt; Is there a way to reference a closed file?

No, INDIRECT requires that the file be open.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;lynncquot; gt; wrote in message
...
gt; Is there a way to reference a closed file?
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt;gt; You need the INDIRECT() function. Bear in mind it doesn't
gt;gt; work when the
gt;gt; workbook you're referencing is closed. If the referenced
gt;gt; workbook is open it
gt;gt; will work just fine.
gt;gt;
gt;gt; =INDIRECT(quot;'[2006 Capacity PMHS Bourbonnais.xls]Capacity
gt;gt; Synopsis'!quot;amp;F$2amp;$A9)
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;lynncquot; wrote:
gt;gt;
gt;gt; gt; I am looking for syntex that will build a formula.
gt;gt; gt;
gt;gt; gt; I want to replace this reference
gt;gt; gt; ='[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!L19
gt;gt; gt;
gt;gt; gt; with a formula that will build the reference using data from
gt;gt; gt; another part of
gt;gt; gt; the worksheet. It would be something like this
gt;gt; gt; CONCATENATE(quot;='[2006 Capacity
gt;gt; gt; PMHS Bourbonnais.xls]Capacity Synopsis'!quot;,F$2,$A9). where
gt;gt; gt; F$2 = L and $A9 =
gt;gt; gt; 19. Only this doesn't work.
gt;gt; gt;
gt;gt; gt;
Chip Pearson wrote...
gt;gt;Is there a way to reference a closed file?
gt;
gt;No, INDIRECT requires that the file be open.
....

But there are add-ins that *do* support this functionality. The best
choice would be Laurent Longre's MOREFUNC.XLL add-in, freely available
from

xcell05.free.fr/english/

Once installed, it provides an add-in function named INDIRECT.EXT which
works just like INDIRECT but supports references into closed workbooks.
And there are other ways to do it using SQL.REQUEST, user-defined
functions written in VBA, and constructing text formulas that look like
external references, then converting them to their values and repacing
= with = to enter then effectively as a batch.