Build Formula

[lynnc]

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.

[Duke Carey]

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.
>
>

[lynnc]

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]

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>> >
>> >

[Harlan Grove]

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.