Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Breaking up Column | Excel Discussion (Misc queries) | |||
Breaking the scale | Excel Discussion (Misc queries) | |||
Breaking Links | Excel Discussion (Misc queries) | |||
Breaking Out of an If Statement | Excel Programming | |||
breaking out of a loop | Excel Programming |