Index Problem

[Tracey]

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 formul
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

 

[T. Valko]

Try it like this:

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

 

[Tracey]

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

I thought I found the error in that I had not calculated as an array (ctlr -
alt - enter) but when I did so I got

17-Feb
Mary
Liz

So it looks like for some reason it is selecting the index below the one it
should be selecting. What am I doing wrong now? Thanks for your help and
expertise.

Tracey

 

[T. Valko]

P.S.

Don't forget to enter as an array.

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Tracey]

Thanks, Did just what I wanted.

Thanks Again....

 

[T. Valko]

You're welcome. Thanks for the feedback!