Excell 2000 - Cell calculations do not function correctly
I haven't the time to research this one, therefore I need help from the experts.
Creating a worksheet that calculates values both across as well as down. As an example:
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| | | |total|
The value of "sum" is cell 1*cell 2*cell 3
Initially sum calculates correctly, however if I change either the quantity of value figures, sum does not recalculate (or update).
What needs to be changed?
Dennis
Creating a worksheet that calculates values both across as well as down. As an example:
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| quantity | value | 1.15 | sum |
| | | |total|
The value of "sum" is cell 1*cell 2*cell 3
Initially sum calculates correctly, however if I change either the quantity of value figures, sum does not recalculate (or update).
What needs to be changed?
Dennis
ASKER
That's what I thought it was originally Cal, but no go, it's set to auto.
I was already surprised by the person asking this kind of question ;-)
Can you mail me the workbook?
calacuccia@hotmail.com
What happens if you hit CTRL+ALT+F9 ?
Can you mail me the workbook?
calacuccia@hotmail.com
What happens if you hit CTRL+ALT+F9 ?
ASKER
Well Cal, there are those times that output takes priority over why something doesn't work as it should. If I can, I'll strip out the proprietary stuff and send it to you.
Do you have the cell values as number and not text values (hey I know this is a long shot here - but . . .)
Have you tried the CTRL+ALT+F9 thing?
Usually, this solves all calculation problems or bugs (and they exist, see http://support.microsoft.com/support/kb/articles/Q171/3/39.ASP)
Usually, this solves all calculation problems or bugs (and they exist, see http://support.microsoft.com/support/kb/articles/Q171/3/39.ASP)
ASKER
Cal, CTRL+ALT+F9 does update the sum.
Shekerra, the values in the cells are set to numeric.
Cal, the article really doesn't apply here as this is Office 2000 with all of the latest updates and fixes.
Shekerra, the values in the cells are set to numeric.
Cal, the article really doesn't apply here as this is Office 2000 with all of the latest updates and fixes.
I'm not surprised the article does not apply, it was more meaned as an example, there are some others I know of:
When Using 'Natural Language Formulas'
http://support.microsoft.com/support/kb/articles/Q200/6/88.ASP
This article shows a method to programmatically force Excel to recalculate, using the Application.CalculateFull method.
Is your problem (the urgent one) solved now?
When Using 'Natural Language Formulas'
http://support.microsoft.com/support/kb/articles/Q200/6/88.ASP
This article shows a method to programmatically force Excel to recalculate, using the Application.CalculateFull method.
Is your problem (the urgent one) solved now?
Were all of the values typed in or pasted from something else?
If you retype all (ALL) of the values (ALL), will it recalc?
If that does nothing...
Can you copy the contents and paste them into a fresh sheet?
If you retype all (ALL) of the values (ALL), will it recalc?
If that does nothing...
Can you copy the contents and paste them into a fresh sheet?
Hmmmm do you have Office 2000 patched?
Perhaps you have an addin that turns calculation off?
How about putting this statement at the top of VBA function:
Application.Volatile (True)
Per chance are you using autofilter?
Perhaps you have an addin that turns calculation off?
How about putting this statement at the top of VBA function:
Application.Volatile (True)
Per chance are you using autofilter?
Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub
ActiveSheet.Calculate
End Sub
shekerra,
Application.Volatile (True) is a dangerous thing to do, especially when working with big workbooks.
It means that ALL functions are recalculated whenever any change is made to the workbook. This means slow performance. I would not use this as long as not needed absolutely.
Application.Volatile (True) is a dangerous thing to do, especially when working with big workbooks.
It means that ALL functions are recalculated whenever any change is made to the workbook. This means slow performance. I would not use this as long as not needed absolutely.
OK Cal agreed - I wasn't aware that this was a big WB.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmmm, okay group, gimme time to go through some of this.
ASKER
Sure, it had to be the last thing I checked on this list. Thanks gang.
Well found, shekerra. I did not see this link in my queeste.
Cal - I found it on technet :)
It seems like you have calculation set to manual.
Go to Tools/Options and on the tab 'Calculation', check the option 'Automatic'.
You can also hit F9 to force a manual recalculation, but if you set it to Automatic as described above, it should work.
calacuccia