Re: Excel help

Topics

Kaffs

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

SazBombMember

Has it copied the cell numbers from the original row into all of the total colums?

so row 1 would be A say, and the total is 50
but 2 should say B and total 100 - it might say A and be adding up the wrnog figures?

11/4/106:03 AM

KaffsMember 10 years

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.

11/4/106:08 AM

El LoroMember VIP10 yearsModerator

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.

11/4/109:00 AM

Ad-Free	Search Site	Start Dialogs
Upload Photos	Upload Videos	Upload Audio
Upload Documents	Use Signature	Block Members
View Member Directory	Mark All Topics As Read	Edit Posts Anytime
Post To Walls

Skip to main content

Topics

Add Reply

Membership Required

Remove From Your Block List

Manage Follow Preferences

Block

Premium Membership Required

VIP

Please wait...