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\Bu dgets\[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\Bu dgets\[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\Bu dgets\[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\Bu dgets\[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