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.

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.

.

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

Luke M wrote:
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.
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]

.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

Have you tried DataFilterAutofilter 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

On Mon, 30 Nov 2009 15:21:18 GMT, "cathyong" <u56573@uwe 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.

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.

Gord Dibben wrote:
Have you tried DataFilterAutofilter 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.

--
Message posted via http://www.officekb.com