Problem with file link

  • Thread starter Thread starter Dervil
  • Start date Start date
D

Dervil

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
 
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?
 
Change:
=SUMIF('[File1.xls]Sheet1'!$B$2:$B$88,$A1,'[File1.xls]Sheet1'!$G$2:$G$88)
t
=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 said:
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 said:
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
Click to expand...
Click to expand...
 
Great!! Back in business - Thanks

Luke M said:
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 said:
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 said:
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

:

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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top