Inserting worksheet makes formulae stop working

[Mark]

I have several worksheets which follow the same pattern, like a checklist.
There's another sheet which summarizes what's on the other sheets using
=Sum(BeginSheet:EndSheet!A1), for instance. When I copy BeginSheet and put
it somewhere between BeginSheet and EndSheet, the formulae on the summary
sheet fails to see the values entered in the new sheet. I can get the
summary to start working again by simply pressing F2 to edit the formula in a
cell, then pressing Enter and accepting what's already there. The value
updates immediately.

Is there a way to get around this, or am I doing something wrong here?

Thank you!

 

[Jason]

Hi Mark, your method should work and automatically calculate as long as you
have automatic calculation turned on on the calculation tab in your options.

 

[Dave Peterson]

I'd double check to make sure that the calculation mode is set to automatic.

In xl2003:
tools|Options|calculation tab

 

[Mark]

Thanks for the response.

My calc mode is set to automatic, so that's not it.

 

[Mark]

That's what I thought, too, but my calc mode is set to automatic and it's
definitely not working.

 

[Dave Peterson]

I don't have a guess why it doesn't reflect automatically.

But you could try this:

Select the worksheet with the formulas on them
Select all the cells on that worksheet
Edit|Replace
what: =
with: =
replace all

It'll force excel to see each of those formulas as a new formula and it'll
recalc. And maybe, just maybe, it'll wake up excel's calculation engine.

Then try inserting/copying another sheet in that sandwich of sheets and make a
few changes to see if that helped.