Problem with file link
 

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?

Problem with file link
 

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?

Problem with file link
 

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?

Problem with file link
 

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?

Problem with file link
 

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?