Index Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tracey - 01 Feb 2008 21:41 GMT

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

Reply to this Message

T. Valko - 01 Feb 2008 22:11 GMT

Try it like this:

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

Signature

Biff
Microsoft Excel MVP

>I am working on a spreadsheet (excel 2002) showing work schedules (A or P).
> On the first sheet I have:
[quoted text clipped - 25 lines]
>
> Tracey

Reply to this Message

T. Valko - 01 Feb 2008 22:17 GMT

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)

Signature

Biff
Microsoft Excel MVP

> Try it like this:
>
[quoted text clipped - 30 lines]
>>
>> Tracey

Reply to this Message

Tracey - 01 Feb 2008 22:22 GMT

Thanks, Did just what I wanted.

Thanks Again....

> Try it like this:
>
[quoted text clipped - 29 lines]
> >
> > Tracey

Reply to this Message

T. Valko - 01 Feb 2008 22:27 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Thanks, Did just what I wanted.
>
[quoted text clipped - 34 lines]
>> >
>> > Tracey

Reply to this Message

Tracey - 01 Feb 2008 22:11 GMT

> I am working on a spreadsheet (excel 2002) showing work schedules (A or P).
> On the first sheet I have:
[quoted text clipped - 23 lines]
>
> 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

Reply to this Message