Thanks, Did just what I wanted.

Thanks Again....

"T. Valko" wrote:

Try it like this:

=IF(..................,ROW(C$2:C$7)-MIN(ROW(C$2:C$7))+1),..........)

--
Biff
Microsoft Excel MVP


"Tracey" wrote in message
...
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