I have a workbook with many worksheets in it. It also has a "Summary"
worksheet that has a lot of formulas referring to these other
worksheets. For instance, one of the formulas on the Summary worksheet
is...

=NPM!$P$4+NPM!$Q$1

NPM is the name of another worksheet in the same workbook. When the
workbook is saved these sheets do not exist. They are created by a
large VBA sub that runs every night. When the sub is complete, all of
the sheets exist and the formulas all work fine.

Periodically Excel "updates" all of these formulas automatically --
not the numbers in them, the actual formula itself. If this happens
before the worksheet in question has been created, it replaces all of
the "NPM!"s with "[NPM]NPM!"s, believing the reference is to an
external workbook. I have no idea why it would believe this to be the
case, but this breaks all of the formulas which have to be hand-edited
to make them work again.

Hand editing them is a chore. If you simply remove the "[NPM]" it
opens the "update links" dialog, and if you press cancel it reverts
the formula and puts the "[NPM]" back in again.

I have found that I can fix this temporarily by first creating a blank
NPM worksheet and then fixing the formulas. Now it works for a short
period of time, but as soon as I remove the blank worksheet, all of
the formulas are updated to replace the correct "NPM!whatever" with
"#REF!whatever", thereby breaking them "even more".

I have been trying to fix this problem and return the spreadsheet to a
working state again, but every edit I make either makes the problem
worse, or breaks some other set of formulas.

This is EXTREMELY FRUSTRATING!

Does anyone know what is causing this to happen, and how to fix it?

Maury