Formulas still breaking
 

Re-starting an older thread...

I have a workbook "template" that consists of a Summary worksheet and
a LOT of VBA code. Depending on inputs from another program (stored in
a db), the VBA code will open the template and generate one or more
new worksheets in the book. They have names like "Alt" and "NP".

The Summary sheet refers to cells in the sheets that may or may not be
created. For instance, one of the cells on the Summary sheet says:

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

If the Alt sheet was created by the VBA code, then this cell will show
the sum of those two cells. If not, it shows #REF, no big deal. There
are about four dozen of these formulas on the Summary sheet.

Periodically Excel will modify the formula without user input. It will
replace the sheet name with a new reference to the sheet's name as if
it were a workbook name, like this...

=[Alt]Alt!$P$4+[Alt]Alt!$Q$1

Whenever this happens, any recalc on the summary page will cause the
"Update Values" open file dialog box to appear. If you Cancel, which
is all you can do, then it leaves the [Alt] in there. If you try to
remove the [Alt] by any means, Search and Replace or hand-editing, the
dialog box appears again, and Canceling once again re-inserts the
bogus book reference.

Can anyone suggest why this is happening?

And how to fix it? Someone suggested using =INDIRECT, but that did not
have any effect.

Maury