Running Totals

[freddie mitchell]

I would like to create a running total for a single cell which then adds up in another!
Example I add "4" to cell b5 this then appears in cell b6. I then add "5" to cell b5 then "9" apperas in b6

How would I get this to work.
Thankyou so much for any assitance freddie

 

[GQue]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Row = 5 And Target.Cells.Column = 2 Then
    Cells(6, 2) = Cells(5, 2) + Cells(6, 2)
End If
End Sub

This will look at B5 and on each change, it will add that value to the value in B6... Hope that helps...

 

[freddie mitchell]

Thankyou so much! would I just copy it if I wanted to do it for multiple examples? Also me being stupid but how do I change the cell numbers ive tired a few times but no luck!

 

[GQue]

The ROW numbers are the same and the columns are numeric for the letter.

IE: A5 = Row 5, Column 1
F10 = Row 10, Column 6

You can put that macro in the sheet that you want it to work with and the IF... THEN will need to be modified for each Row/Column Combination you want...

In my example:

If Target.Cells.Row = 5 And Target.Cells.Column = 2 Then

That is checking B5 for a change. For F6 you would use

If Target.Cells.Row = 6 And Target.Cells.Column = 6 Then

Make sense?

If you are looking to watch BOTH cells for changes, the code would be

Private Sub Worksheet_Change(ByVal Target As Range)
'Watch B5 for Changes, Running total in B6
If Target.Cells.Row = 5 And Target.Cells.Column = 2 Then
    Cells(6, 2) = Cells(5, 2) + Cells(6, 2)
End If
'Watch F6 for Changes, Running total in F7
If Target.Cells.Row = 6 And Target.Cells.Column = 6 Then
    Cells(7, 6) = Cells(6, 6) + Cells(7, 6)
End If
End Sub

That answer your question?

 

[freddie mitchell]

Thankyou for the name changing I almost got it right :P!! But how do actaully copy the formula and use it on the same spreadsheet (right now its telling me that theres an amigious name ending) Thankyou

 

[GQue]

This is not a formula, this is a macro.

The macro is a OnChange macro, so whenever data is changed in the sheet, it triggers the macro, that is why there is that If the cells is a certain row and column, then do something, otherwise ignore.

I assumed you would want to watch multiple cells, so that is why I added that last example in my previous post.

This will be placed in the VBE (Visual Basic Environment). You can get there by pressing ALT-F11.

 

[freddie mitchell]

Thankyou so much all sorted now!

 

[GQue]

Great... Glad I could help out!

 

[freddie mitchell]

I now have another problem! is there any way of connecting an excel document with word! i.e. someone rights somethin g in a text box on excel then they press return and it gets moved to a word document? thanks for any assitance

 

[GQue]

That is something I never thought of doing and quite frankly, don't think it it possible. If someone else knows how, then they are a better man than me.

Why, if I may ask, are you looking to do that? I think you may be able to do something similiar with a Mail Merge feature where you setup a field in the word doc that pulls from an Excel sheet, but I don't believe it is dynamic.

Good luck with that one... =)