1 Then Exit Sub ' value to look for ... if a changed cell contains KEY then +1 the corresponding row in offset column Const KEY = "X" ' user input is col C... we want to change corresponding row in F therefore col offset is 3 Const COL_OFFSET = -1 .">
• Hot Topics

# Excel Counting how many times a cell is changed to a certain value using a formula and not cell selection

Hello Everyone First of all i want to thank the community for all the help you guys have given me indirectly. I usually browse around for hours looking for an answer that suits my needs instead of creating a new thread, but this time I am pretty lost. Basically I am going to use a hand held scanner to input data into one cell (B1) in a spreadsheet. The data is going to be composed of natural numbers(no fractions/negatives). I wrote a formula [=IF(C3=\$B\$1,"X"," ")] in the "B" row(B3:B650) that checks weather the adjacent number in the "C" row(C3:C650) matches the value of the (B1) cell. If it does, then it returns an "X". If it doesn't then it returns a blank value. 1 2 3 4 5 6 7 I have been using the following code which i found in another thread: -------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub ' value to look for ... if a changed cell contains KEY then +1 the corresponding row in offset column Const KEY = "X" ' user input is col C... we want to change corresponding row in F therefore col offset is 3 Const COL_OFFSET = -1