simple formulas are not calculating

K

Kevin

A co-worker of mine is working in a file that has no
external links. The calculations are rather basic. On
an INCONSISTANT basis the spreadsheet fails to update
calculated values. Said differently, if a hard coded
number is changed on the input tab the resulting
calculation on the output tab does not update for the new
data. Manually forcing a calcuation (F9) has no effect.
The "calculation" option is set correctly. There are no
tables involved. Performing a "save" has no effect.
However, performing a "save as" forces the updated
calculation.

Any thoughts?
 
D

Dave Peterson

Maybe forcing a complete recalc will help:

From xl2002's help:

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last
calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of
whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.


I think that last one was added in xl2002.

If those didn't help, how about grouping all your sheets (click on the first and
shift click on the last)
ctrl-A (twice in xl2003) to select all the cells.
Edit|replace
what: =
with: =
(Yep. Equal sign with equal sign)

Ungroup your sheets and see if that helped.

By doing the mass change, maybe it forced xl to rebuilt its dependency table.
(but maybe not, too!)
 
D

David McRitchie

Are these Worksheet Functions or are some of them User Defined
Functions. If they are all Worksheet Functions the most aggressive
of the recalculate shortcuts may break things loose.

If you have User Defined Functions they will not recalculate automatically.

In any case Functions should name their ranges rather than just
putting a range hidden within the UDF, so that a change will be
recognized and force a recalculation when needed.

The functions available in Excel 2000 is a bit less you don't have
the CTRL+SHIFT+ALT+F9 but you do have the others.
more on my http://www.mvps.org/dmcritchie/excel/shortx2k.htm#calc
if you have Excel 2000.
 
Top