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

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

"Tracey" wrote:

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

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)

--
Biff
Microsoft Excel MVP


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

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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