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.
OzGrid Business Applications
Posted by Dave on January 05, 2001 5:08 PM
OOPS!!
That should read: right click on the sheet name tab and select "View Code" then paste it over the top of what you see.
OzGrid Business Applications
