Why will links not update unless source is open?

[Guest]

What does it signify when a spreadsheet will not update from its links unless
both spreadsheets involved are open under Excel? Sometimes this happens,
sometimes it doesn’t. I cannot tell the divergence between the two
situations. “value†is the usual error message in each cell with a link.

 

[T. Valko]

What's the formula look like? Sumif and Countif will act like that.

Biff

 

[Guest]

Hi WesJFrank;

Ensure that you have set the folowing settings before save the Workbook:

1. On the Tools menu, click Options
The Options dialog box appears.
2. In the Options dialog box, click the “Calculations†Tab.
3. Under Calculation, click the Automatic
4. Under the Workbook Options, select the ‘Update remote references†and
“Save the external link values†check boxes
5. Click OK

Challa Prabhu

 

[Guest]

I have the same problem and have changed my settings to what you've outlined
and I'm still having trouble. My spreadsheet requires that the other
worksheet be open in order for the links to work. Any suggestions?

 

[Guest]

If you're using SUMIF or COUNTIF then these formulas don't work when
referencing external workbooks - they only work when the source workbook is
open. Try using SUM and IF separately (eg =SUM(IF(...)) instead of
=SUMIF(...)), or SUMPRODUCT.

Mel