Problem with file link

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?
 
T

Tom Hutchins

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
 
D

Dervil

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?
 
L

Luke M

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
 
D

Dervil

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top