Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas mysteriously break
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas mysteriously break
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. -- Gary "Maury Markowitz" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas mysteriously break
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 "Maury Markowitz" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas mysteriously break
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 "Jim Thomlinson" wrote: 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 "Maury Markowitz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row mysteriously getting deleted | Excel Discussion (Misc queries) | |||
Break Links But Keep Formulas | Excel Discussion (Misc queries) | |||
Date changes mysteriously | Excel Worksheet Functions | |||
Column mysteriously shifts up/down | Excel Discussion (Misc queries) | |||
userform mysteriously unloads | Excel Programming |