Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Over-written formulas
I, along with some help from others who know a lot more about Excel than I
do, have created a batch of forms using Excel and VB code. We have a bug that none of us can figure out. There is a formula in a cell that takes the value of another cell on a different tab (a dollar amount) and raises that amount to the next $500 {=IF('Cost Est'!G76=0,0,'Cost Est'!G76-MOD('Cost Est'!G76,500)+500}. The problem is, sometimes that formula is over-written with a value. Therefore, if changes are made to the €śCost Est€ť sheet, the changes are not reflected in the cell. This doesnt happen every time and we cant seem to figure out what is making it happen. We dont believe that this is occurring because of any code because it has happened even when the code is disabled. We have searched the sheet and the code to see if we can find anything that might over-write a cells formula and have been unsuccessful. If anyone has any ideas, please let me know. Id be glad to send you the forms if that will help. (There is nothing confidential involved.) Also, please note that we are using a very old version (2000) of Microsoft. What can I say, were a State government agency and a little behind the times. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Over-written formulas
If anyone has any ideas, please let me know ..
One thought: impose a control, then monitor closely whether it still recurs. Password protect the formulas on that sheet, with the password strictly restricted to a single person/his cover. If it doesn't recur after that, you've isolated the cause - human error. Any good? hit the YES below, -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "dcornett63" wrote: I, along with some help from others who know a lot more about Excel than I do, have created a batch of forms using Excel and VB code. We have a bug that none of us can figure out. There is a formula in a cell that takes the value of another cell on a different tab (a dollar amount) and raises that amount to the next $500 {=IF('Cost Est'!G76=0,0,'Cost Est'!G76-MOD('Cost Est'!G76,500)+500}. The problem is, sometimes that formula is over-written with a value. Therefore, if changes are made to the €śCost Est€ť sheet, the changes are not reflected in the cell. This doesnt happen every time and we cant seem to figure out what is making it happen. We dont believe that this is occurring because of any code because it has happened even when the code is disabled. We have searched the sheet and the code to see if we can find anything that might over-write a cells formula and have been unsuccessful. If anyone has any ideas, please let me know. Id be glad to send you the forms if that will help. (There is nothing confidential involved.) Also, please note that we are using a very old version (2000) of Microsoft. What can I say, were a State government agency and a little behind the times. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Over-written formulas
Max,
thanks for the response. Actually, this is occurring even in the testing stages while I am testing my code. So, I know that it isn't someone going in and overriding my formulas. However, I may have (thanks to your response) figured out the problem. It may have something to do with data valadation. I had been using data valadation to restrict the user to decimals (to restrict them from entering text into this field) and to let the user know that the cell will self-populate from another tab but can be overwritten if necessary. I have now removed the data valadation and am using a comment to notify them. It seems to be working at the moment, but as I stated in the original message, it doesn't occur every time so I can't really be sure that the problem is fixed or if it's just not happening right now. Guess we'll see. Thanks again for the help "Max" wrote: If anyone has any ideas, please let me know .. One thought: impose a control, then monitor closely whether it still recurs. Password protect the formulas on that sheet, with the password strictly restricted to a single person/his cover. If it doesn't recur after that, you've isolated the cause - human error. Any good? hit the YES below, -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "dcornett63" wrote: I, along with some help from others who know a lot more about Excel than I do, have created a batch of forms using Excel and VB code. We have a bug that none of us can figure out. There is a formula in a cell that takes the value of another cell on a different tab (a dollar amount) and raises that amount to the next $500 {=IF('Cost Est'!G76=0,0,'Cost Est'!G76-MOD('Cost Est'!G76,500)+500}. The problem is, sometimes that formula is over-written with a value. Therefore, if changes are made to the €śCost Est€ť sheet, the changes are not reflected in the cell. This doesnt happen every time and we cant seem to figure out what is making it happen. We dont believe that this is occurring because of any code because it has happened even when the code is disabled. We have searched the sheet and the code to see if we can find anything that might over-write a cells formula and have been unsuccessful. If anyone has any ideas, please let me know. Id be glad to send you the forms if that will help. (There is nothing confidential involved.) Also, please note that we are using a very old version (2000) of Microsoft. What can I say, were a State government agency and a little behind the times. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use functions that I have written in Excel formulas | Excel Worksheet Functions | |||
How do I input these formulas into cells that I want written in? | Excel Worksheet Functions | |||
Written values | Excel Worksheet Functions | |||
Hidden rows unhide themselves when formulas are written | Excel Discussion (Misc queries) | |||
How do i calculate Written Down value | Excel Worksheet Functions |