I have a worksheet that lists all the members of our church. Across the top
are all the Sundays in the year. Next to each person's name, I am filling
the sheet with either an A (Absent) or P (present). To the far left, I have
a column called quot;number of consecutive absencesquot;. What I need is a formula
to count the number of consecutive absences from the most reason Sunday.
Something like this:
John Smith A P A A P A A A 3
In the example John has missed three consecutive Sundays. If he is present
at the next meeting, the number would reset to 0. If he is absent, it would
then be 4. I don't need to count the total number of absences, just the
number of absences since the last attendance.
The purpose is to help our outreach teams keep in contact. When a person is
absent, we want to send a card, but when a person is absent twice in a row we
want to make a call...three times...a visit...and so on. This worksheet will
make it a lot easier to keep track of what the teams should be doing.
Thanks in advance for your help.
Jerry
Easily achieved with VBA. Assuming that the dates start in column C and the
absence count is in column B
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim nCount As Long
Dim nMax As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column gt; 2 Then
With Target
For i = 3 To Me.Cells(1, Columns.Count).End(xlToLeft).Column
If Cells(.Row, i).Value = quot;aquot; Then
nCount = nCount 1
Else
If nCount gt; nMax Then
nMax = nCount
End If
nCount = 0
End If
Next i
Me.Cells(.Row, quot;Bquot;).Value = nMax
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;jerry37917quot; gt; wrote in message
...
gt; I have a worksheet that lists all the members of our church. Across the
top
gt; are all the Sundays in the year. Next to each person's name, I am filling
gt; the sheet with either an A (Absent) or P (present). To the far left, I
have
gt; a column called quot;number of consecutive absencesquot;. What I need is a
formula
gt; to count the number of consecutive absences from the most reason Sunday.
gt; Something like this:
gt;
gt; John Smith A P A A P A A A
3
gt;
gt; In the example John has missed three consecutive Sundays. If he is
present
gt; at the next meeting, the number would reset to 0. If he is absent, it
would
gt; then be 4. I don't need to count the total number of absences, just the
gt; number of absences since the last attendance.
gt;
gt; The purpose is to help our outreach teams keep in contact. When a person
is
gt; absent, we want to send a card, but when a person is absent twice in a row
we
gt; want to make a call...three times...a visit...and so on. This worksheet
will
gt; make it a lot easier to keep track of what the teams should be doing.
gt;
gt; Thanks in advance for your help.
gt;
gt; Jerry
Hi Jerry
You could do this with Conditional Formatting
Assuming your data starts in B2, then the earliest value that could have
three consecutive quot;Aquot;'s is D2.
Also assuming you have 100 rows with data
Highlight D2:BB101
Formatgt;Conditional Formattinggt;use dropdown to select Formual is and in
the whit epane type
=SUMPRODUCT(--(B22=quot;Aquot;))=3
Choose Format and select either a coloured Font or Red or Background of
Red
Click the Add button and repeat the above but set the last part of
formula =2, and a format of Yellow
Click Add button, change last part of formula to =1 and set Format to
Green--
Regards
Roger Govier
jerry37917 gt; wrote:
gt; I have a worksheet that lists all the members of our church. Across
gt; the top are all the Sundays in the year. Next to each person's name,
gt; I am filling the sheet with either an A (Absent) or P (present). To
gt; the far left, I have a column called quot;number of consecutive
gt; absencesquot;. What I need is a formula to count the number of
gt; consecutive absences from the most reason Sunday. Something like this:
gt;
gt; John Smith A P A A P A A A
gt; 3
gt;
gt; In the example John has missed three consecutive Sundays. If he is
gt; present at the next meeting, the number would reset to 0. If he is
gt; absent, it would then be 4. I don't need to count the total number
gt; of absences, just the number of absences since the last attendance.
gt;
gt; The purpose is to help our outreach teams keep in contact. When a
gt; person is absent, we want to send a card, but when a person is absent
gt; twice in a row we want to make a call...three times...a visit...and
gt; so on. This worksheet will make it a lot easier to keep track of
gt; what the teams should be doing.
gt;
gt; Thanks in advance for your help.
gt;
gt; Jerry
Thank you very much. Your code wasn't exactly what I was looking for, but it
gave me what I needed. I was able to modify it slighly and achieve the count
that I was wanting. I am relatively new to Excel and didn't even know this
was possible. Wow! I didn't realize just how powerful Excel really is.
Thanks again.
Jerry
quot;Bob Phillipsquot; wrote:
gt; Easily achieved with VBA. Assuming that the dates start in column C and the
gt; absence count is in column B
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Dim i As Long
gt; Dim nCount As Long
gt; Dim nMax As Long
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Target.Column gt; 2 Then
gt; With Target
gt; For i = 3 To Me.Cells(1, Columns.Count).End(xlToLeft).Column
gt; If Cells(.Row, i).Value = quot;aquot; Then
gt; nCount = nCount 1
gt; Else
gt; If nCount gt; nMax Then
gt; nMax = nCount
gt; End If
gt; nCount = 0
gt; End If
gt; Next i
gt; Me.Cells(.Row, quot;Bquot;).Value = nMax
gt; End With
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;jerry37917quot; gt; wrote in message
gt; ...
gt; gt; I have a worksheet that lists all the members of our church. Across the
gt; top
gt; gt; are all the Sundays in the year. Next to each person's name, I am filling
gt; gt; the sheet with either an A (Absent) or P (present). To the far left, I
gt; have
gt; gt; a column called quot;number of consecutive absencesquot;. What I need is a
gt; formula
gt; gt; to count the number of consecutive absences from the most reason Sunday.
gt; gt; Something like this:
gt; gt;
gt; gt; John Smith A P A A P A A A
gt; 3
gt; gt;
gt; gt; In the example John has missed three consecutive Sundays. If he is
gt; present
gt; gt; at the next meeting, the number would reset to 0. If he is absent, it
gt; would
gt; gt; then be 4. I don't need to count the total number of absences, just the
gt; gt; number of absences since the last attendance.
gt; gt;
gt; gt; The purpose is to help our outreach teams keep in contact. When a person
gt; is
gt; gt; absent, we want to send a card, but when a person is absent twice in a row
gt; we
gt; gt; want to make a call...three times...a visit...and so on. This worksheet
gt; will
gt; gt; make it a lot easier to keep track of what the teams should be doing.
gt; gt;
gt; gt; Thanks in advance for your help.
gt; gt;
gt; gt; Jerry
gt;
gt;
gt;
That is just scratching the surface lt;bggt;
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;jerry37917quot; gt; wrote in message
...
gt; Thank you very much. Your code wasn't exactly what I was looking for, but
it
gt; gave me what I needed. I was able to modify it slighly and achieve the
count
gt; that I was wanting. I am relatively new to Excel and didn't even know this
gt; was possible. Wow! I didn't realize just how powerful Excel really is.
gt; Thanks again.
gt;
gt; Jerry
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Easily achieved with VBA. Assuming that the dates start in column C and
the
gt; gt; absence count is in column B
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; Dim i As Long
gt; gt; Dim nCount As Long
gt; gt; Dim nMax As Long
gt; gt;
gt; gt; On Error GoTo ws_exit:
gt; gt; Application.EnableEvents = False
gt; gt; If Target.Column gt; 2 Then
gt; gt; With Target
gt; gt; For i = 3 To Me.Cells(1, Columns.Count).End(xlToLeft).Column
gt; gt; If Cells(.Row, i).Value = quot;aquot; Then
gt; gt; nCount = nCount 1
gt; gt; Else
gt; gt; If nCount gt; nMax Then
gt; gt; nMax = nCount
gt; gt; End If
gt; gt; nCount = 0
gt; gt; End If
gt; gt; Next i
gt; gt; Me.Cells(.Row, quot;Bquot;).Value = nMax
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt; ws_exit:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;jerry37917quot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have a worksheet that lists all the members of our church. Across
the
gt; gt; top
gt; gt; gt; are all the Sundays in the year. Next to each person's name, I am
filling
gt; gt; gt; the sheet with either an A (Absent) or P (present). To the far left,
I
gt; gt; have
gt; gt; gt; a column called quot;number of consecutive absencesquot;. What I need is a
gt; gt; formula
gt; gt; gt; to count the number of consecutive absences from the most reason
Sunday.
gt; gt; gt; Something like this:
gt; gt; gt;
gt; gt; gt; John Smith A P A A P A A A
gt; gt; 3
gt; gt; gt;
gt; gt; gt; In the example John has missed three consecutive Sundays. If he is
gt; gt; present
gt; gt; gt; at the next meeting, the number would reset to 0. If he is absent, it
gt; gt; would
gt; gt; gt; then be 4. I don't need to count the total number of absences, just
the
gt; gt; gt; number of absences since the last attendance.
gt; gt; gt;
gt; gt; gt; The purpose is to help our outreach teams keep in contact. When a
person
gt; gt; is
gt; gt; gt; absent, we want to send a card, but when a person is absent twice in a
row
gt; gt; we
gt; gt; gt; want to make a call...three times...a visit...and so on. This
worksheet
gt; gt; will
gt; gt; gt; make it a lot easier to keep track of what the teams should be doing.
gt; gt; gt;
gt; gt; gt; Thanks in advance for your help.
gt; gt; gt;
gt; gt; gt; Jerry
gt; gt;
gt; gt;
gt; gt;
Maybe...
=IF(COUNTIF(B1:BA1,quot;Pquot;),COUNTIF(INDEX(B1:BA1,MATCH (2,1/(B1:BA1=quot;Pquot;))):BA1
,quot;Aquot;),COUNTIF(B1:BA1,quot;Aquot;))
....confirmed with CONTROL SHIFT ENTER, not just ENTER. Adjust the range
accordingly.
Hope this helps!
In article gt;,
quot;jerry37917quot; gt; wrote:
gt; I have a worksheet that lists all the members of our church. Across the top
gt; are all the Sundays in the year. Next to each person's name, I am filling
gt; the sheet with either an A (Absent) or P (present). To the far left, I have
gt; a column called quot;number of consecutive absencesquot;. What I need is a formula
gt; to count the number of consecutive absences from the most reason Sunday.
gt; Something like this:
gt;
gt; John Smith A P A A P A A A 3
gt;
gt; In the example John has missed three consecutive Sundays. If he is present
gt; at the next meeting, the number would reset to 0. If he is absent, it would
gt; then be 4. I don't need to count the total number of absences, just the
gt; number of absences since the last attendance.
gt;
gt; The purpose is to help our outreach teams keep in contact. When a person is
gt; absent, we want to send a card, but when a person is absent twice in a row we
gt; want to make a call...three times...a visit...and so on. This worksheet will
gt; make it a lot easier to keep track of what the teams should be doing.
gt;
gt; Thanks in advance for your help.
gt;
gt; Jerry
Domenic,
I had a similar issue and just tried your solution. It worked perfect! I'm
trying to read through the function to try to understand what you actually
did. Haven't quite figured it out yet, but it works anyway!
Thanks again.
quot;Domenicquot; wrote:
gt; Maybe...
gt;
gt; =IF(COUNTIF(B1:BA1,quot;Pquot;),COUNTIF(INDEX(B1:BA1,MATCH (2,1/(B1:BA1=quot;Pquot;))):BA1
gt; ,quot;Aquot;),COUNTIF(B1:BA1,quot;Aquot;))
gt;
gt; ....confirmed with CONTROL SHIFT ENTER, not just ENTER. Adjust the range
gt; accordingly.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;jerry37917quot; gt; wrote:
gt;
gt; gt; I have a worksheet that lists all the members of our church. Across the top
gt; gt; are all the Sundays in the year. Next to each person's name, I am filling
gt; gt; the sheet with either an A (Absent) or P (present). To the far left, I have
gt; gt; a column called quot;number of consecutive absencesquot;. What I need is a formula
gt; gt; to count the number of consecutive absences from the most reason Sunday.
gt; gt; Something like this:
gt; gt;
gt; gt; John Smith A P A A P A A A 3
gt; gt;
gt; gt; In the example John has missed three consecutive Sundays. If he is present
gt; gt; at the next meeting, the number would reset to 0. If he is absent, it would
gt; gt; then be 4. I don't need to count the total number of absences, just the
gt; gt; number of absences since the last attendance.
gt; gt;
gt; gt; The purpose is to help our outreach teams keep in contact. When a person is
gt; gt; absent, we want to send a card, but when a person is absent twice in a row we
gt; gt; want to make a call...three times...a visit...and so on. This worksheet will
gt; gt; make it a lot easier to keep track of what the teams should be doing.
gt; gt;
gt; gt; Thanks in advance for your help.
gt; gt;
gt; gt; Jerry
gt;
留言列表
