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("='[2006 Capacity
PMHS Bourbonnais.xls]Capacity Synopsis'!",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("'[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!"&F$2&$A9)



"lynnc" wrote:

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("='[2006 Capacity
PMHS Bourbonnais.xls]Capacity Synopsis'!",F$2,$A9). where F$2 = L and $A9 =
19. Only this doesn't work.

Is there a way to reference a closed file?

"Duke Carey" wrote:

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("'[2006 Capacity PMHS Bourbonnais.xls]Capacity Synopsis'!"&F$2&$A9)



"lynnc" wrote:

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("='[2006 Capacity
PMHS Bourbonnais.xls]Capacity Synopsis'!",F$2,$A9). where F$2 = L and $A9 =
19. Only this doesn't work.

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



"lynnc" wrote in message
...
Is there a way to reference a closed file?

"Duke Carey" wrote:

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("'[2006 Capacity PMHS Bourbonnais.xls]Capacity
Synopsis'!"&F$2&$A9)



"lynnc" wrote:

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("='[2006 Capacity
PMHS Bourbonnais.xls]Capacity Synopsis'!",F$2,$A9). where
F$2 = L and $A9 =
19. Only this doesn't work.

Chip Pearson wrote...
Is there a way to reference a closed file?

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

http://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.