# Sum this - possible/impossible?

Posted by Kristina on January 05, 2001 12:38 PM

I have two fields A1 and B1.
In A1 the value changes, while B1 keeps adding the values entered in field A1.
e.g.

A1 B1
1 1
next time
A1 B1
6 7
and after that
A1 B1
2 9

I got a few suggestions that enabled me to do the correct calculation in a row, but when I change data in the row beneath, all the rows above of the one that is changing change too.
Such as

A1 B1
2 2
A2 B2 B1
3 3 5
A3 B3 B1
1 1 6

I would like that all the rows are treated like separate entities; change in one row doesn't influence the data in the row below or above.
Does anybody have any idea how/if this can be done?
If this is impossible to do in Excel, do you have any idea how/what can I do
this in a simple way? (this calculation is intented for worksheet that can
be send as E-mail attachement and will be used by many people)
Thank you,

Kristina

Posted by greg on January 05, 2001 3:49 PM

response: use the worksheet change event and use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 Then

Range("b1") = Range("b1") + Range("a1")
End If
End Sub

Posted by Dave on January 05, 2001 5:06 PM

Hi Kristina

Rather than using Circular references you would be far better off using the Sheet_Change event.

This example will place in Column B the value of the same row in Column A+ itself.

To use it, right click on the sheet name tab and paste it over the top of what you see.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
With Target
If .Cells.Count = 1 Then
If .Column = 1 Then .Offset(0, 1) = .Offset(0, 1) + .Value
End If
End With
End Sub

BTW don't forget to set your Iterations back to normal, else you will run into all sorts of probs.