Cumulative Total

[Bonnie]

Is thre a way to get a cumulative total in a cell? For
example: If I put a person used 1 hour of sick leave in
January in cell A1 and have cell B1 read 1, then in
February, I put 2 hours in cell A1, could I set B1 so that
it now reads 3 for total sick leave for the year?

Bonnie

 

[Trevor Shuttleworth]

Bonnie

one way:

'Worksheet Class Module
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
[B1] = [B1] + [A1]
End Sub

Regards

Trevor

 

[Guest]

Thanks. That works!

Bonnie

-----Original Message-----
Bonnie

one way:

'Worksheet Class Module
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
[B1] = [B1] + [A1]
End Sub

Regards

Trevor

Is thre a way to get a cumulative total in a cell? For
example: If I put a person used 1 hour of sick leave in
January in cell A1 and have cell B1 read 1, then in
February, I put 2 hours in cell A1, could I set B1 so that
it now reads 3 for total sick leave for the year?

Bonnie

.

 

[Bonnie]

Thanks. That works great. However, we need it in several
rows. Is there a way to make it relative so that it can be
copied down?

-----Original Message-----
Bonnie

one way:

'Worksheet Class Module
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
[B1] = [B1] + [A1]
End Sub

Regards

Trevor

Is thre a way to get a cumulative total in a cell? For
example: If I put a person used 1 hour of sick leave in
January in cell A1 and have cell B1 read 1, then in
February, I put 2 hours in cell A1, could I set B1 so that
it now reads 3 for total sick leave for the year?

Bonnie

.

 

[Trevor Shuttleworth]

Bonnie

this works with any cell in column A and accumulates into the next column,
same row:

'Worksheet Class Module
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Target.Offset(0, 1).Value = _
Target.Offset(0, 1).Value + _
Target.Value
End Sub

Regards

Trevor

Thanks. That works great. However, we need it in several
rows. Is there a way to make it relative so that it can be
copied down?

-----Original Message-----
Bonnie

one way:

'Worksheet Class Module
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
[B1] = [B1] + [A1]
End Sub

Regards

Trevor

Is thre a way to get a cumulative total in a cell? For
example: If I put a person used 1 hour of sick leave in
January in cell A1 and have cell B1 read 1, then in
February, I put 2 hours in cell A1, could I set B1 so that
it now reads 3 for total sick leave for the year?

Bonnie

.