Excel Automatically recalculating succeeding cells

[bill74]

Hi All
Excel 2000
I have bunch of numbers that are laid out in 13 columns by 100 rows. A the end of the last row I autosum to gather the totals, this is working fine. However I need to make a change to one of the totals(this happens to be on the top row) but when I make the change to the formula and hit enter all of the succeeding totals are automatically changed to my new formula (but with the appropraite cell references). I don't want this to happen can anyone help. I assume that I have checked a box in some option menu but I have looked and cant find anything.

Bill

[NBVC]

Can you be more clear? Not sure what you mean by automatically changed to the new formula? Maybe an example?

[bill74]

Hi NBVC

I want to change the top formula =SUM(CT3,GU3) to =SUM(CT3,ET3,GU3) but as soon as I hit enter Excel seems to want to fix all of the succeeding formulas. and include the ET cells reference in all of the succeeding totals
These are all set out in the same column.
=SUM(CT3,GU3)
=SUM(CT4,GU4)
=SUM(CT5,GU5)
=SUM(CT6,GU6)
=SUM(CT7,GU7)
=SUM(CT8,GU8)

Further to my previous info there are 17 Rows by 199 Columns. I have 51 other groups of numbers set out as above the are each separated by a space of three cells. So when I change the top formula all of the succeeding cells are changed.
I have unchecked the "Extend list formats and formulas" in Tools/Options Edit but to no avail.


The crazy thing is that if I open fresh spreadsheet and copy the whole of the worksheet I can then change the top formula and it does not update the other cells.

[NBVC]

Hmmm.. does your range have a blue border around it? I.e. are you using the List feature? Not sure if it was available in 2000?

Is it possible to attach the sheet here (removing confidential data)?

[bill74]

No blue border. Everything seems hunky dory.
Whole workbook is some 43Mb so I won't be able to attach that. If I copy the sheet giving me the problem we will lose the number as they are being taken from another sheet within the workbook.

I have jsut noticed that if I click on th cells and make NO changes and then click off of it, excel then extends everything including the blank three cells space in between number groups.

I will attach a copy of the sheet but there will be #VALUE errors in the cells that contains references to other worksheets, but you may notice something about that is beyond me. I have highlighted in red the problem row. There are no confidential data or data protection infringements contained within the sheet.

Many thanks in advance

[NBVC]

It doesn't seem to happen to me on the attached workbook.

Not sure how to recreate your problem, otherwise? I will think about it more and let you know if I have suggestions...

.... or if anyone else has ideas?

[bill74]

Hi NBVC

Please find attached "Before and After" Screen dumps of what happens when I enter the new formula. You will notice in the second shot, that cells GU20,21 and 22 are then filled. All of the cells in the totals column now include the cell reference ET with the appropraite number. This is what I want to avoid.

Thanks for taking the time to help me, it is very much appreciated.

KR

Bill

[NBVC]

If you right-click on the sheet tab and select View Code... anything come up in the VB Editor window that might cause this?

[bill74]

Hi NBVC

Oh my God, I am a numpty.

Private Sub CommandButton1_Click()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xrange As Range
If Target.HasFormula Then
Application.EnableEvents = False
If Target.Column > 1 Then
Set xrange = Target.Offset(0, -1).End(xlDown).Offset(0, 1)
Range(Target, xrange).FillDown
Application.EnableEvents = True
End If
End If
End Sub

I have disabled this and it now works perfectly.
I don't remember doing this at any point so that is why I would not have thought to look there.

You are fantastic thank you very very much for your help. You just may have saved my sanity.

Kindest Regards

Bill