Detecting absentees

G

gracegift

I am wondering if Excel has the capability of detecting a series of numbers.
For example, I am tracking attendance in a given row with "1" being present
and "0" being absent. When someone is absent for three days straight (0 0 0
in a given row), can Excel give me a warning of some kind? I know that with
Filters, Excel can give various warnings or prompts.

Any help you can provide would be appreciated!
 
F

Frank Kabel

Hi
one way:
- select C1:X1
- goto 'Format - conditional Format' and choose 'Formula is'
- enter the formula:
=COUNTIF(A1:C1,0)=3
- choose your format
 
M

Max

Perhaps 2 options to play around with ..

Assume you have this kind of set-up
in Sheet1, cols A to Z (say),
data in row2 down:

------ 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct 6-Oct >etc
Max 0 1 0 0 0 0
Peter 0 0 0 0 0 1
Tom 1 0 0 0 1 1
Jane 1 1 0 0 0 0
etc

where B1:Z1 will contain the actual dates (in "d-mmm"
format) of all *working days only*

Option 1: Use conditional formatting to highlight the 1st
cell of any 3 consecutive zero cells in-a-row within the
grid (as per your specs)

Select the "inside"** range: B2:Z100 (say)
**range excluding the top row and col A

Click Format > Conditional Formatting
Under Condition 1, make the settings as:
Formula Is |= AND(NOT(OR($A2="",COUNTA(B2:D2)<3)),SUM
(B2:D2)=0)
Click Format button > Patterns tab > brown? > OK
Click OK at the main dialog

For the sample data above, you'll see that cells:

D2, E2,
B3, C3, D3,
C4,
D5 and E5

will be conditionally formatted with brown fill color

Option 2: Extract alert phrases containing the names and
period of any 3 consecutive zero cells in-a-row within the
attendance table in Sheet1

In a new Sheet2
-------------------
Put in A2:

=IF(OR(Sheet1!$A2="",COUNTA(Sheet1!B2:D2)<3),"",IF(SUM
(Sheet1!B2:D2)=0,Sheet1!$A2&": "&TEXT(Sheet1!B$1,"d-mmm")
&" to "&TEXT(Sheet1!D$1,"d-mmm"),""))

Copy across as many cols as you have working dates in
Sheet1, then fill down by as many rows as there are names
expected in col A in Sheet1

The formulas will return the alert phrases such as:

Max: 3-Oct to 5-Oct
Peter: 1-Oct to 3-Oct Peter: 2-Oct to 4-Oct Peter: 3-Oct
to 5-Oct
Tom: 2-Oct to 4-Oct
Jane: 3-Oct to 5-Oct
 
O

Otto Moehrbach

The easiest way I see to do this is to use a Worksheet_Change event macro.
This macro would fire automatically if any cell entry changes, and it would
check to see if the changed cell is within a specific range, and if it is it
would check to see if the entry is zero, and if it is it would check to see
if this zero entry is the third of 3 consecutive zero entries. The result
would be a message box saying whatever you want it to say or maybe an
automatic printout of whatever you want. An example macro is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Column < 3 Then Exit Sub
If Target.Value = 0 Then
If Application.CountA(Target.Offset(, -2).Resize(, 2)) = 2 And _
Application.Sum(Target.Offset(, -2).Resize(, 2)) = 0 And _
IsNumeric(Target.Offset(, -2)) And
IsNumeric(Target.Offset(, -1)) Then
MsgBox "Three in a row at " & Target.Offset(, -2).Resize(,
3).Address(0, 0)
End If
End If
End Sub
Change the A1:G10 range to the range in your data that would hold the 1 and
0 numbers. This macro may need to be massaged a little depending on the
layout of your data. This macro has to be placed in the sheet module of the
sheet that holds your data. Right-click on the sheet tab, select View Code,
and paste this macro into the displayed module. Post back if you need more.
HTH Otto
 
G

gracegift

Thanks for your reply. I appreciate your time!

Max said:
Perhaps 2 options to play around with ..

Assume you have this kind of set-up
in Sheet1, cols A to Z (say),
data in row2 down:

------ 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct 6-Oct >etc
Max 0 1 0 0 0 0
Peter 0 0 0 0 0 1
Tom 1 0 0 0 1 1
Jane 1 1 0 0 0 0
etc

where B1:Z1 will contain the actual dates (in "d-mmm"
format) of all *working days only*

Option 1: Use conditional formatting to highlight the 1st
cell of any 3 consecutive zero cells in-a-row within the
grid (as per your specs)

Select the "inside"** range: B2:Z100 (say)
**range excluding the top row and col A

Click Format > Conditional Formatting
Under Condition 1, make the settings as:
Formula Is |= AND(NOT(OR($A2="",COUNTA(B2:D2)<3)),SUM
(B2:D2)=0)
Click Format button > Patterns tab > brown? > OK
Click OK at the main dialog

For the sample data above, you'll see that cells:

D2, E2,
B3, C3, D3,
C4,
D5 and E5

will be conditionally formatted with brown fill color

Option 2: Extract alert phrases containing the names and
period of any 3 consecutive zero cells in-a-row within the
attendance table in Sheet1

In a new Sheet2
-------------------
Put in A2:

=IF(OR(Sheet1!$A2="",COUNTA(Sheet1!B2:D2)<3),"",IF(SUM
(Sheet1!B2:D2)=0,Sheet1!$A2&": "&TEXT(Sheet1!B$1,"d-mmm")
&" to "&TEXT(Sheet1!D$1,"d-mmm"),""))

Copy across as many cols as you have working dates in
Sheet1, then fill down by as many rows as there are names
expected in col A in Sheet1

The formulas will return the alert phrases such as:

Max: 3-Oct to 5-Oct
Peter: 1-Oct to 3-Oct Peter: 2-Oct to 4-Oct Peter: 3-Oct
to 5-Oct
Tom: 2-Oct to 4-Oct
Jane: 3-Oct to 5-Oct
Click to expand...
 
G

gracegift

Thanks for your reply! I appreciate your time!

Otto Moehrbach said:
The easiest way I see to do this is to use a Worksheet_Change event macro.
This macro would fire automatically if any cell entry changes, and it would
check to see if the changed cell is within a specific range, and if it is it
would check to see if the entry is zero, and if it is it would check to see
if this zero entry is the third of 3 consecutive zero entries. The result
would be a message box saying whatever you want it to say or maybe an
automatic printout of whatever you want. An example macro is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Column < 3 Then Exit Sub
If Target.Value = 0 Then
If Application.CountA(Target.Offset(, -2).Resize(, 2)) = 2 And _
Application.Sum(Target.Offset(, -2).Resize(, 2)) = 0 And _
IsNumeric(Target.Offset(, -2)) And
IsNumeric(Target.Offset(, -1)) Then
MsgBox "Three in a row at " & Target.Offset(, -2).Resize(,
3).Address(0, 0)
End If
End If
End Sub
Change the A1:G10 range to the range in your data that would hold the 1 and
0 numbers. This macro may need to be massaged a little depending on the
layout of your data. This macro has to be placed in the sheet module of the
sheet that holds your data. Right-click on the sheet tab, select View Code,
and paste this macro into the displayed module. Post back if you need more.
HTH Otto
Click to expand...
 
Top