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('Sh eet
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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cathyong" wrote:

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.

.