attendance with sorting

[cathyong]

hi. Needs help. I have an attendance workbook with the 400 names listed as
present, absent or excused. Our office needs to see at a glance who are
absent for the day instead of looking at the data with 400 names. Is there a
way to have Excel automatically list those absent for particular in separate
worksheet or have those absent sorted in separate worksheet automatically. As
of now, we will copy and paste the names and their attendance everyday and
sort.

 

[Luke M]

This is an array* formula. Assumes Names are in column A of Sheet 1, and
absent/present data is in C1:C10 of sheet 1. Pay no attention to the
ROWS(A1:A1) and ROW(A1) callouts, they are there to perform counting
operations.

=IF(COUNTIF('Sheet 1'!$C$1:$C$10,"absent")<ROWS($A$1:A1),"",INDEX('Sheet
1'!A:A,SMALL(IF('Sheet 1'!$C$1:$C$10="absent",ROW('Sheet
1'!$C$1:$C$10)),ROW(A1))))

Copy down formula far enough to account for the largest number of absences
you would have.

 

[Gord Dibben]

Have you tried Data>Filter>Autofilter for "absent" or "present" or "excused"
with a subtotal(3,range)?

You could record a macro for each of the the three strings.

A button for each.


Gord Dibben MS Excel MVP

 

[cathyong via OfficeKB.com]

Hi Luke M

names are in col A , present and absent date are in col B to col E
where shall I paste the array formula?

Thanks

This is an array* formula. Assumes Names are in column A of Sheet 1, and
absent/present data is in C1:C10 of sheet 1. Pay no attention to the
ROWS(A1:A1) and ROW(A1) callouts, they are there to perform counting
operations.

=IF(COUNTIF('Sheet 1'!$C$1:$C$10,"absent")<ROWS($A$1:A1),"",INDEX('Sheet
1'!A:A,SMALL(IF('Sheet 1'!$C$1:$C$10="absent",ROW('Sheet
1'!$C$1:$C$10)),ROW(A1))))

Copy down formula far enough to account for the largest number of absences
you would have.

hi. Needs help. I have an attendance workbook with the 400 names listed as
present, absent or excused. Our office needs to see at a glance who are

[quoted text clipped - 5 lines]

.

 

[cathyong via OfficeKB.com]

Hi I tried out Dat, Filter, Autofilter for subtotal (3, range). It works out
fine.
Thanks.

I do you write a macro for the 3 strings.

Have you tried Data>Filter>Autofilter for "absent" or "present" or "excused"
with a subtotal(3,range)?

You could record a macro for each of the the three strings.

A button for each.

Gord Dibben MS Excel MVP

hi. Needs help. I have an attendance workbook with the 400 names listed as
present, absent or excused. Our office needs to see at a glance who are

[quoted text clipped - 3 lines]

of now, we will copy and paste the names and their attendance everyday and
sort.