Cumulative Sum

[rbobcat1]

Trying to setup a cumulative sum but cant get it right.
a b c d
Denom Qty Total Qty Total
1.00 1 1 1.00

But on day 2 b2 changes and i put 4, c2 changes to 5 and d2 changes to 5.00.
on day 3 b2 changes to 4, c2 becomes 9 and d2 becomes 9.00


I have posted an example spreadsheet on SkyDrive and shared it to everyone.
https://skydrive.live.com/redir.aspx?cid=2b770c659a94550d&resid=2B770C659A94550D!423&parid=root

 

[Blade Hunter]

You need to use absoluting.

Something like:

D2: =Sum(D$2:D2)

then drag down The $ tells it not to increment that reference.

 

[rbobcat1]

You need to use absoluting.

Something like:

D2: =Sum(D$2:D2)

then drag down The $ tells it not to increment that reference.

that does not keep a cumulative total of how many i have collected in c2 if i change b2.

 

[rbobcat1]

i have changed the file post to skydrive
https://skydrive.live.com/redir.aspx?cid=2b770c659a94550d&resid=2B770C659A94550D!424&parid=root

 

[Blade Hunter]

Oh my mistake, so when you update something you want the total to equal its original value PLUS the new value?

In such a case you will need to use a sheet level macro using the worksheet_change event. Easy enough to do but quite open to error (ie if you enter something wrong then type over it again both the wrong and the correct amounts will be added)

 

[rbobcat1]

yes that is what i want to do

 

[Blade Hunter]

Right click on the tab and choose "View code"

Paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 2 Then Range("C2").Formula = Range("C2").Value + Range("B2").Value
End Sub

 

[rbobcat1]

That worked, Great, now if i want it to for other rows i just change the row reference?

 

[Blade Hunter]

Yeah but you could give it a range like this:

If target.row > 1 and target.row < 11 and Target.column = 2 then blah blah

That will work on rows 2 to 10 for column B

Edit: You need to change the reference for posting the data to also, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Row < 11 And Target.Column = 2 Then Range("C" & Target.Row).Formula = Range("C" & Target.Row).Value + Range("B" & Target.Row).Value
End Sub