I am working on a spreadsheet (excel 2002) showing work schedules (A or P).
On the first sheet I have:
17-Feb 18-Feb 19-Feb 20-Feb
Sue RN a a a
Mary RN p p p
Betty RN a a a
Peter RN p p p
Paul RN a a a
Liz RN p p p

On sheet 2 I want to list those working the A shift by day so it should show:

17-Feb 18-Feb 19-Feb 20-Feb
Sue Sue Sue Betty
Paul Betty Betty Paul
Paul

I am using the following formula
A2:=IF(ROWS($1:1)COUNTIF(Sheet1!$C$2:$C$7,"a"),"" ,INDEX(Sheet1!$A$2:$A$7,SMALL(IF((Sheet1!$C$2:$C$7 ="a"), ROW(Sheet1!$C$2:$C$7)),ROWS($1:1))))

This formula is copied down the columns.
A2 result is "Mary"
A3 is #NUM

Can anyone tell me what I'm doing wrong? Thanks ahead of time.

Tracey