Skip to main content

OK - I need help from some of you boffins out there.    Excel has lost the plot and I don't know what's wrong.


I'm totally rows of figures but when I copy the formula down the page - it copies the formula into the cells, but it puts the sum from the top row into them!   Let me explain


row 1 ... columns  10   10  10  10 10        sum = 50
row 2     columns   20   20  20  20 20       Sum = 100 BUT... it shows 50 in the total cell (but the formula has copied correctly)     


I thought I was going mental... but nobody here has seen this happen before.   If it helps make sense - if you're in the total cell and click into the formula bar it corrects the calculation.   I'm scared now... if this is happening in all my spreadsheets we could be going down the tubes and I'd never know....  


*waits for the lovely experts*

Replies sorted oldest to newest

Hi Saz and thanks.   Nope the forumulas look correct and clicking in the formula bar forces it to update.   I think I may have sussed it though.....    somehow the tools/option/calculate function is set to manual.    Changed it to automatic and it seems ok.   ( I bloomin' love the internet!)    Scary how that could happen without my knowledge though and if it hadn't been so glaringly obvious with the thing I was doing the model would be totally screwed.
Kaffs
I have found this mentioned on a website:

Auto_Open set to guarantee Calculation is turned on (#autocalc)

I had trouble with Recalculation being turned off that I put code into Auto_Open to fix it.  Turned out that it was an Addin that I wasn't even using that was causing this.

    Sub auto_open()
If Application.Calculation <> -4105 Then
'-4105 automatic, -4135 manual, 2 semi-automatic
MsgBox Application.Calculation & " <calculation in> " & _
ActiveWorkbook.FullName
Application.Calculation = xlAutomatic
MsgBox Application.Calculation
End IF
End Sub

So it looks as if your problem may have been caused by an Addin, but it doesn't say which one. The code shown would be a macro that someone has written but I have not tested it and I have no idea what the -4105 and -4135 is referring to.
El Loro

Add Reply

×
×
×
×
Link copied to your clipboard.
×
×