SOLVED

Count every time a new value is entered in a Cell

Copper Contributor

Hello all,

 

I hope I am able to explain what I want to do properly, so here it goes:

 

I have two cells that I want to count the values of, but the second cell is constantly being updated with a new value (a date). I want to count in a third cell the value on the first cell plus the number of times a new value was put in the second cell. So for example, there is a date in the first cell, the second cell had a date as well one day, two days later another date is put in that cell, the third cell should have a final count of 3 (instead of 2 which would happen if I used the COUNT formula).

 

I honestly don't know if this is possible but if it isn't any advice would be appreciated.

 

Thank you!

6 Replies

@DianaRato 

Could you please provide small sample file to illustrate the question?

Hi Sergei,

Here is a video, in the third cell I want to appear 3 for the total number of values that were put in those cells.

https://www.loom.com/share/85e7528b1d7142f988b45218b82244a5

@DianaRato 

I see, thank you. That's with VBA programming. Sorry, not my territory, perhaps someone else could help.

best response confirmed by DianaRato (Copper Contributor)
Solution

Hello @DianaRato,

 

Enter this VBA code as a procedure in the necessary worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Sheets("Sheet1").Range("A1"), Target) Is Nothing Then
Range("B1").Value = Range("B1").Value + 1
End If
End Sub

 Where...

  • "Sheet1" is the sheet that you want to reference
  • Range("A1") is the range of cells that you want to check for changes
  • Range("B1") is the cell that will add one for each change that is made in the range of cells that you want to check for changes
My pleasure!
1 best response

Accepted Solutions
best response confirmed by DianaRato (Copper Contributor)
Solution

Hello @DianaRato,

 

Enter this VBA code as a procedure in the necessary worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Sheets("Sheet1").Range("A1"), Target) Is Nothing Then
Range("B1").Value = Range("B1").Value + 1
End If
End Sub

 Where...

  • "Sheet1" is the sheet that you want to reference
  • Range("A1") is the range of cells that you want to check for changes
  • Range("B1") is the cell that will add one for each change that is made in the range of cells that you want to check for changes

View solution in original post