I have several linked Excel files in an Excel workbook, some of these files
themselves are linked to others.
Up until recently there was no problem but now when I open the workbook I
get the error #VALUE! unless I open the linked file (and then usually the
files which are linked to that file as well!).
I end up with lots of files opened which I really don't want, added to the
frustration of having to do this each time.
I'm using Windows XP Professional/Excel 2003.
Anyone know what the problem might be?

Some functions, such as INDIRECT and SUMIF, need linked workbooks open to
return a value. If your links are in formulas with this limitation, that
would cause the #VALUE error if the linked workbooks aren't also open.

SUMIF can be replaced with SUMPRODUCT, which does work with closed
workbooks. Without knowing the nature of your links (which formulas are using
them), it's hard to give specific advice.

Hope this helps,

Hutch

"Dervil" wrote:

I have several linked Excel files in an Excel workbook, some of these files
themselves are linked to others.
Up until recently there was no problem but now when I open the workbook I
get the error #VALUE! unless I open the linked file (and then usually the
files which are linked to that file as well!).
I end up with lots of files opened which I really don't want, added to the
frustration of having to do this each time.
I'm using Windows XP Professional/Excel 2003.
Anyone know what the problem might be?

Thanks Tom - that explains it! I'm using SUMIF. This is the type of formula:
SUMIF('[File1.xls]Sheet1'!$B$2:$B$88,$A1,'[File1.xls]Sheet1'!$G$2:$G$88)

Not sure if I substitute SUMPRODUCT for SUMIF that it will work?

"Tom Hutchins" wrote:

Some functions, such as INDIRECT and SUMIF, need linked workbooks open to
return a value. If your links are in formulas with this limitation, that
would cause the #VALUE error if the linked workbooks aren't also open.

SUMIF can be replaced with SUMPRODUCT, which does work with closed
workbooks. Without knowing the nature of your links (which formulas are using
them), it's hard to give specific advice.

Hope this helps,

Hutch

"Dervil" wrote:

I have several linked Excel files in an Excel workbook, some of these files
themselves are linked to others.
Up until recently there was no problem but now when I open the workbook I
get the error #VALUE! unless I open the linked file (and then usually the
files which are linked to that file as well!).
I end up with lots of files opened which I really don't want, added to the
frustration of having to do this each time.
I'm using Windows XP Professional/Excel 2003.
Anyone know what the problem might be?

Change:
=SUMIF('[File1.xls]Sheet1'!$B$2:$B$88,$A1,'[File1.xls]Sheet1'!$G$2:$G$88)
to
=SUMPRODUCT(--('[File1.xls]Sheet1'!$B$2:$B$88=$A1),('[File1.xls]Sheet1'!$G$2:$G$88))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dervil" wrote:

Thanks Tom - that explains it! I'm using SUMIF. This is the type of formula:
SUMIF('[File1.xls]Sheet1'!$B$2:$B$88,$A1,'[File1.xls]Sheet1'!$G$2:$G$88)

Not sure if I substitute SUMPRODUCT for SUMIF that it will work?

"Tom Hutchins" wrote:

Some functions, such as INDIRECT and SUMIF, need linked workbooks open to
return a value. If your links are in formulas with this limitation, that
would cause the #VALUE error if the linked workbooks aren't also open.

SUMIF can be replaced with SUMPRODUCT, which does work with closed
workbooks. Without knowing the nature of your links (which formulas are using
them), it's hard to give specific advice.

Hope this helps,

Hutch

"Dervil" wrote:

I have several linked Excel files in an Excel workbook, some of these files
themselves are linked to others.
Up until recently there was no problem but now when I open the workbook I
get the error #VALUE! unless I open the linked file (and then usually the
files which are linked to that file as well!).
I end up with lots of files opened which I really don't want, added to the
frustration of having to do this each time.
I'm using Windows XP Professional/Excel 2003.
Anyone know what the problem might be?

Great!! Back in business - Thanks

"Luke M" wrote:

Change:
=SUMIF('[File1.xls]Sheet1'!$B$2:$B$88,$A1,'[File1.xls]Sheet1'!$G$2:$G$88)
to
=SUMPRODUCT(--('[File1.xls]Sheet1'!$B$2:$B$88=$A1),('[File1.xls]Sheet1'!$G$2:$G$88))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dervil" wrote:

Thanks Tom - that explains it! I'm using SUMIF. This is the type of formula:
SUMIF('[File1.xls]Sheet1'!$B$2:$B$88,$A1,'[File1.xls]Sheet1'!$G$2:$G$88)

Not sure if I substitute SUMPRODUCT for SUMIF that it will work?

"Tom Hutchins" wrote:

Some functions, such as INDIRECT and SUMIF, need linked workbooks open to
return a value. If your links are in formulas with this limitation, that
would cause the #VALUE error if the linked workbooks aren't also open.

SUMIF can be replaced with SUMPRODUCT, which does work with closed
workbooks. Without knowing the nature of your links (which formulas are using
them), it's hard to give specific advice.

Hope this helps,

Hutch

"Dervil" wrote:

I have several linked Excel files in an Excel workbook, some of these files
themselves are linked to others.
Up until recently there was no problem but now when I open the workbook I
get the error #VALUE! unless I open the linked file (and then usually the
files which are linked to that file as well!).
I end up with lots of files opened which I really don't want, added to the
frustration of having to do this each time.
I'm using Windows XP Professional/Excel 2003.
Anyone know what the problem might be?