Hello
I have the following custom function which works when all linked files are
open in the same excel session.
=Sum2DProduct('V:\CompanyName\Accounts.09\Budgets\[Rolling
budget.xlsm]P&L'!$C$8:$BB$8,E$8,'V:\CompanyName\Accounts.09\Budgets\[Rolling
budget.xlsm]P&L'!$BD$10:$BD$75,$BD11,'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$10:$BB$75)

When the files are closed excel returns #VALUE and the function does not
run. I have replaced the mapped drive letter with the full path but no change.

Is there a way of writing the address below so that the linked file does not
have to be open for excel to be happy and the function to run?

'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$8:$BB$8
or with named range
'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]'!Profit

Hope this makes sence.
Thanks

Corrected link

http://xcell05.free.fr/morefunc/english/index.htm
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Cresta" wrote:

> Hello
> I have the following custom function which works when all linked files are
> open in the same excel session.
> =Sum2DProduct('V:\CompanyName\Accounts.09\Budgets\[Rolling
> budget.xlsm]P&L'!$C$8:$BB$8,E$8,'V:\CompanyName\Accounts.09\Budgets\[Rolling
> budget.xlsm]P&L'!$BD$10:$BD$75,$BD11,'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$10:$BB$75)
>
> When the files are closed excel returns #VALUE and the function does not
> run. I have replaced the mapped drive letter with the full path but no change.
>
> Is there a way of writing the address below so that the linked file does not
> have to be open for excel to be happy and the function to run?
>
> 'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$8:$BB$8
> or with named range
> 'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]'!Profit
>
> Hope this makes sence.
> Thanks
>

I believe you'd need something like the Indirect.Ext functoin found in
MOREFUNC here.

http://xcell05.free.fr/english/
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Cresta" wrote:

> Hello
> I have the following custom function which works when all linked files are
> open in the same excel session.
> =Sum2DProduct('V:\CompanyName\Accounts.09\Budgets\[Rolling
> budget.xlsm]P&L'!$C$8:$BB$8,E$8,'V:\CompanyName\Accounts.09\Budgets\[Rolling
> budget.xlsm]P&L'!$BD$10:$BD$75,$BD11,'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$10:$BB$75)
>
> When the files are closed excel returns #VALUE and the function does not
> run. I have replaced the mapped drive letter with the full path but no change.
>
> Is there a way of writing the address below so that the linked file does not
> have to be open for excel to be happy and the function to run?
>
> 'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$8:$BB$8
> or with named range
> 'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]'!Profit
>
> Hope this makes sence.
> Thanks
>

It sounds like the right function but the download link is out of action.
Typical.



"Barb Reinhardt" wrote:

> Corrected link
>
> http://xcell05.free.fr/morefunc/english/index.htm
> --
> HTH,
> Barb Reinhardt
>
> If this post was helpful to you, please click YES below.
>
>
>
> "Cresta" wrote:
>
> > Hello
> > I have the following custom function which works when all linked files are
> > open in the same excel session.
> > =Sum2DProduct('V:\CompanyName\Accounts.09\Budgets\[Rolling
> > budget.xlsm]P&L'!$C$8:$BB$8,E$8,'V:\CompanyName\Accounts.09\Budgets\[Rolling
> > budget.xlsm]P&L'!$BD$10:$BD$75,$BD11,'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$10:$BB$75)
> >
> > When the files are closed excel returns #VALUE and the function does not
> > run. I have replaced the mapped drive letter with the full path but no change.
> >
> > Is there a way of writing the address below so that the linked file does not
> > have to be open for excel to be happy and the function to run?
> >
> > 'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]P&L'!$C$8:$BB$8
> > or with named range
> > 'V:\CompanyName\Accounts.09\Budgets\[Rolling budget.xlsm]'!Profit
> >
> > Hope this makes sence.
> > Thanks
> >