Formulas mysteriously break

[Maury Markowitz]

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

 

[Gary Keramidas]

just a question, why couldn't you just write some code to insert the formulas
when you want them. i know in some instances i have created the formulas in the
cells. but then i write code to recreate all of them with the click of a button,
just in case something or someone trashes the formulas.

it may be worth the time to do this, since you say it happens way too often.

 

[Jim Thomlinson]

Deleting the worksheet breaks all of the links. Just putting a sheet back and
re-naming it does not unbreak those links. The only feasable solution that I
see is to modify the VBA not not delete the sheet. You should only be
clearing it's contents.

What you see as a cell address is translated to a memory address where the
cell is storing the value. When you delete the sheet you are wiping out that
memory. When you create the new sheet your system allocates new memory for it
and the memory assocated with the cells will in all likelyhood bare no
relationship to the memory allocated to the old worksheet.

 

[Jim Thomlinson]

There could be one other solution to look at and that would be to use the
indirect function. The downside to this is that indirect has a lot of
overhead as it is a volatile function (must be recaluated every time a
calculation runs)

=indirect("NPM!$P$4")+indirect("NPM!$Q$1")

--
HTH...

Jim Thomlinson

Deleting the worksheet breaks all of the links. Just putting a sheet back and
re-naming it does not unbreak those links. The only feasable solution that I
see is to modify the VBA not not delete the sheet. You should only be
clearing it's contents.

What you see as a cell address is translated to a memory address where the
cell is storing the value. When you delete the sheet you are wiping out that
memory. When you create the new sheet your system allocates new memory for it
and the memory assocated with the cells will in all likelyhood bare no
relationship to the memory allocated to the old worksheet.
--
HTH...

Jim Thomlinson

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