This one comes up all the time, and I thought it was worth a post. You enter a formula and it calculates correctly. Then you fill or copy the formula and you get the same answer, but you know the answer should be different.
Nine times out of 10, the problem is that calculation is set to manual. Take a look at the lower left of your spreadsheet.
If it says “Calculate”, then calculation is set to manual and it needs to be automatic if you expect the formulas to change. Go to Tools > Options > Calculation and select the Automatic option button.
Dick…one of the guys on the VBAExpress board helped me with this macro to set up a MANUAL/AUTOMATIC calculation button. Just create a new toolbar called “Calcs” and set up a button with this macro:
Sub ManAuto()
With Application
If .Calculation = xlCalculationManual Then
.Calculation = xlCalculationAutomatic
Set myControl1 = CommandBars(“Calcs”).Controls(1)
myControl1.State = msoButtonUp
Else
.Calculation = xlCalculationManual
Set myControl1 = CommandBars(“Calcs”).Controls(1)
myControl1.State = msoButtonDown
End If
End With
End Sub
I have fun with this little problem. I use it at work many times. It is a very helpful tool. You just need to be aware of what mode you’re in. We have several people at my work that chastise me for my Excel skills. They consider themselves experts. Well, I leave the spreadsheet in Manual calc and pass it off to these so called Excel experts. Then, I really get an ear full about the spreadsheet not working. “Here you go, genius, press the button.”
Or, you can press F9 to recalculate everything that needs calculating, or press shift-F9 to just recalculate the current sheet.
help me. I have created a list of 10 and 11 (differetn eyar groups). I then want to count the poeple, if they chose the option 10. It wont work! My email address is hunter15_91@hotmail.com