Rolling Yearly Totals

[kagribble]

I am using Excel 2003, and I have a problem which involves rolling yearly totals. I have a sheet that is similar to the example below..

1 2 3
2 5 7

3 7 10 (Subtotal 1)

1 3 4
5 7 9

6 10 13 (Subtotal 2)

9 17 23 (Running Total)

My problem is that I want the value in the running total to not change while I enter different data for the other numbers. I have SUM formulas on rows 4 and 9 and then on row 11 totaling row 4 and 9.

I would like row 11 to remain constant when I clear the rest of the table. I know that I can copy/paste special/values on that row to keep the numbers but is there any way I can format those values to change when different data is put in?

Sorry if this is an easy one. I just can't figure it out or if it can work period. Thank you for your help. I'm ready to pull my hair out.
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64 x:num></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64 x:num></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num></TD></TR></TBODY></TABLE>

 

[James01]

Perhaps the solution is to change calculation from automatic to manual.

Tools --> Options---> Calculations-->select manual

Excel will not recaluclate the formulas until you press F9.

Hope that helped save a lot of hair :wink:

 

[jimbojones]

Strange thing to want to be able to do. You could use a macro to calculate the values to populate in row 11 when required. Something like:

Sub Update_Totals()

Range("A11").Value = Range("A4").Value + Range("A9").Value
Range("B11").Value = Range("B4").Value + Range("B9").Value

End Sub

would work. Run the macro when you want to update row 11. Still not sure why you would want to do this though.

 

[kagribble]

The reason I am trying to do it like this is because I have a different worksheet every month, but I need to run a yearly total. Like for January if my totals are

12 14 18

I want to be able to add the subtotals from February to the total but put the figures from February on the chart. Would it be easier to perhaps put all months on separate workbook sheets and link each grand total to the sheet before?