Formula to keep adding a 1 to a cell based on another cell value

[Malcolm torishi]

Hi
can anyone tell me if there is a formula that constantly adds a 1 to a cell value each time a 1 appears in another cell. EG
columnA Row 2:4 has 3 people’s names in each cell , column B row 2:4 as a 1 in each cell, each time the person in column A leaves the building, what I want to do is in column C have a running total of the number of times that person as left the building. So each time a person leaves there will show 1 next to his name in column B and when he returns the 1 gets deleted but if he goes out again a 1 will appear in column B again and I want to constantly keep adding the number of times he goes out to column C
thank you

 

[Richard U]

your best bet would be to use the change event on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Target, Columns(2)) Is Nothing Then
    If Target = 1 Then Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + 1
End If

End Sub

 

[Malcolm torishi]

your best bet would be to use the change event on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Target, Columns(2)) Is Nothing Then
    If Target = 1 Then Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + 1
End If

End Sub

Hi Richard
this Code works perfectly when the value is entered as a digit, but I have a vlookup formula and it just doesn’t want to work. I have even changed the top part of the code to
Private Sub Worksheet _calculate but still does not what to work,
does anyone have any idea what to do please
thank you

 

[Malcolm torishi]

Hello can any please help with this been looking all day if an answer , but I just can not sort. I would really appreciate help, all I need is the code to work with a vlookup on my spreadsheet.

 

[Malcolm torishi]

Hi
I am still trying to sort this but just can’t. Can anyone help, main issue is getting this to work with a vlookup, can anyone help please . I would so much appreciate it. Thank you in advance.

 

[footoo]

Here's a possibility that involves using a helper column (which could be hidden).
Assuming column D is the helper column, to set-up enter in C2:C4 the values that apear in B2:B4, then :

Private Sub Worksheet_Calculate()
Dim rng As Range, cel As Range
Set rng = [C2:C4]
For Each cel In rng
    If cel(1, 0) = 1 And cel(1, 2) = 0 Then
        cel = cel + 1
        cel(1, 2) = 1
    Else: cel(1, 2) = 0
    End If
Next
End Sub

 

[footoo]

Revised :

Assuming column D is the helper column, to set-up enter in D2:D4 the values that appear in B2:B4, then :

Private Sub Worksheet_Calculate()
Dim rng As Range, cel As Range
Set rng = [C2:C4]
For Each cel In rng
    If cel(1, 0) = 1 And cel(1, 2) = "" Then
        cel = cel + 1
        cel(1, 2) = 1
    ElseIf cel(1, 0) = "" And cel(1, 2) = 1 Then cel(1, 2) = ""
    End If
Next
End Sub