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 [I][=IF(C3=$B$1,"X"," ")][/I] 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.

A B C D E
1 SCANNER INPUT
2 COUNTER SCANNED NUMBER
3 [I]=IF(C3=$B$1,"X"," ")[/I] 10
4 [I]=IF(C4=$B$1,"X"," ")[/I] 11
5 [I]=IF(C5=$B$1,"X"," ")[/I] 12
6 [I]=IF(C6=$B$1,"X"," ")[/I] 13
7 [I]=IF(C7=$B$1,"X"," ")[/I] 14
I have been using the following code which i found in another thread: -------------------------- [I]Private Sub Worksheet_Change(ByVal Target As Range)[/I] [I] If Target.Count > 1 Then Exit Sub[/I] [I] ' value to look for ... if a changed cell contains KEY then +1 the corresponding row in offset column[/I] [I] Const KEY = "X"[/I] [I] ' user input is col C... we want to change corresponding row in F therefore col offset is 3[/I] [I] Const COL_OFFSET = -1[/I] [I] Dim userInputRng As Range: Set userInputRng = Me.Range("B3:B650")[/I] [I] Dim inputTest As Range: Set inputTest = Intersect(Target, userInputRng)[/I] [I] If inputTest Is Nothing Then Exit Sub[/I] [I] If UCase(Target.Value) = UCase(KEY) Then Target.Offset(0, COL_OFFSET).Value = Target.Offset(0, COL_OFFSET).Value + 1[/I] [I]End Sub ----------------------------[/I] The problem is that the code only executes the counting whenever i select the cell and either type an "X" in it or if it already has "X" and i press enter. I want the code to do the counting if the cell is modified by the formula in the "B" column. ie. If i scan an 11 into B2, and thus B4 goes through its "=IF" formula and returns an "X" value, i want the counter in A4 to increase. I hope I have made myself clear. Thanks in advance!


This question generated 31 answers. To proceed to the answers, click here.

This thread is current as of June 06, 2014.


For more resources for Microsoft Excel