Share via

Formulas dont work when a file is closed

Anonymous
2011-09-22T01:20:47+00:00

Sometimes when I have a formula like SUMIF that links to another file, when I close that file and hit F9 to calculate the values disappear and I get #VALUE.

How can I stop that from happening without getting rid of the formulas?

I have a lot of files like this that when I open them and make a change and calculate formulas in other cells return #VALUE until I open the linked file.

All help appreciated.

Rick

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-09-22T01:41:27+00:00

Certain formulas simply don't work when the linked file is not open.  What exactly would you prefer happen when those formulas can't be updated?  We can't display the value that was previously calculated, so you kind of have the choice of not updating the links and preserving the previous value, or handling the error in some other way such as wrapping your problem formulas in the Excel 2007/2010 IFERROR() function, like

=IFERROR(SUMIF(....),0)

which would return 0 when the SUMIF(...) formula generates an error.

You might go into [File] --> Options --> Advanced and scroll down to the When calculating this workbook: section and making sure that the "Update links to other documents" and "Save external link values" are both checked.  Then you might see how things work for you if you clear the first one (Update...) but that may force you to manually update those values.  Not sure ... haven't played with it all much and am not sure of how things actually work with various combinations of those two option settings.

Was this answer helpful?

2 people found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest