Function Help

[Steven Cooper]

I need to create an algorith that will increment a counter every time
a watched cell contains a particular vaule. For exampe, if cell a1 =
"H" then the counter would be incremented by one. Is there a way to
easily accomplish this?

 

[JE McGimpsey]

How often would you be "watching"?

Every time the sheet calculates? Every time a value is entered into the
watched cell?

One way would be to use circular references. Choose
Tools/Options/Calculation and check the iteration checkbox and enter 1
for max iterations. Assuming the "watched" cell is A1, put this in B1:

=IF(A1="H",B1+1,B1)


now when you enter H (and whenever the sheet is recalculated) 1 will be
added to B1.

 

[Steven Cooper]

Thank you, let me further explain. The "H" or the alternative an "M",
hits or misses, represents the successful completion of commitment
milestones, a combination of project milestones, audit commitments,
etc. The goal is to demonstrate accountability to completing the item
on time.

With projects, it's permissible to move milestone dates occasionally,
if the are probativly adjusted, not waiting for them to become
overdue. So a date may go "red" on several occasions. The objective
is to track how many times the date is missed with out the
demonstrated accountability. So, the watched cell could only update
when the value changes, not every time the sheet re-calculates.

Any help is appreciated, as this is now a manual process, tracking
thirty resources and hundreds of commitments.

Regards,
-sdc-

 

[JE McGimpsey]

Then you'll probably want to use VBA. Put this in your worksheet code
module (right-click the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "A1" Then
If UCase(CStr(Target.Value)) = "H" Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + 1
Application.EnableEvents = True
End If
End If
End Sub

 

[Steven Cooper]

I believe this to be the solution. How might I deal with multiple
watched cells? A1:A10 related to B1:B10 or even A:A to B:B?

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A:B")) Is Nothing Then
If UCase(CStr(.Value)) = "H" Then
Application.EnableEvents = False
Range("C1").Value = Range("C1").Value + 1
Application.EnableEvents = True
End If
End If
End With
End Sub