Multiple IF factors

[kamille824]

I have a spreadsheet with
A - Day of week
B - Time of Day
C - etc - Employee names
Day Time Amy P Angel M Chad S Deborah J
Monday 6:00 AM AUX
Monday 6:15 AM AUX
Monday 6:30 AM AUX
Monday 6:45 AM AUX
Monday 7:00 AM AUX
Monday 7:15 AM AUX
Monday 7:30 AM Phone AUX
Monday 7:45 AM Phone AUX
Monday 8:00 AM Phone Break
Monday 8:15 AM Phone AUX
Monday 8:30 AM Aux Phone AUX
Monday 8:45 AM AUX Phone AUX
Monday 9:00 AM AUX Phone AUX
Monday 9:15 AM AUX Phone Phone
Monday 9:30 AM AUX Break Phone
Monday 9:45 AM AUX Phone Phone


Rows indicate what job function they are doing each 15 minutes throughout
the day.

I need to create a formula that will tell me which employees are doing a
particular job function (break, lunch, etc), on a specific day (Tues) for
a certain time (9:15 am)
I have a separate worksheet started with
Day of week, job function and time and list each employee under it,
Monday
Phones
10:00 AM

Amy P ?????
Angel M ?????
Chad S ?????
Deborah J ???
Can anyone help me?

 

[Biff]

Hi!

This solution is based on your sample data as posted.

Assume your table is on Sheet1 A1:F17.

On Sheet2 you have in:

A1 = Monday
A2 = Phone
A3 = 9:45 AM
A4 = (blank)

In A5 enter this formula with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(Sheet1!C$1:F$1,SMALL(IF((Sheet1!A$2:A$17=A$1)*
(Sheet1!B$2:B$17=A$3)*(Sheet1!C$2:F$17=A$2),COLUMN
(A)),ROW(1:1)))

Copy down until you get #NUM! errors.

This formula will return:

A5 = Angel M
A6 = Deborah J
A7 = #NUM!

You can suppress the #NUM! errors by using an error trap
in the formula but that will make the formula twice as
long:

=IF(ISERROR(SMALL(IF((Sheet1!A$2:A$17=A$1)*(Sheet1!
B$2:B$17=A$3)*(Sheet1!C$2:F$17=A$2),COLUMN(A)),ROW
(1:1))),"",INDEX(Sheet1!C$1:F$1,SMALL(IF((Sheet1!
A$2:A$17=A$1)*(Sheet1!B$2:B$17=A$3)*(Sheet1!
C$2:F$17=A$2),COLUMN(A)),ROW(1:1))))

An alternative is to use the shorter formula and then use
conditional formatting to hide the errors. Example:

If you would normally expect to have 5 employees that meet
the criteria of Monday, Phone, 9:45 AM, then you would
want to copy the formula to AT LEAST 5 cells, So:

Select the range A5:A9
Goto Format>Conditional Formatting
Formula is: =ISERROR(A5)
Click the Format button.
Set the font color to be the same as the fill color.
OK out.

You could also make the formula much shorter by using
defined named ranges.

Biff

 

[kamille824]

Thanks Biff, but I still have something wrong as I can't get the data to
fill is after 10:00 am. I have 286 rows of info and Columns thru "S"
The formula I used was:
=IF(ISERROR(SMALL(IF((Schedule!A$2:A$286=A$1)*(Schedule!B$2:B$286=A$3)*(Schedule!C$2:S$286=A$2),Column(A:Q)),ROW(1:1))),"",INDEX(Schedule!C$1:S$1,SMALL(IF((Schedule!A$2:A$286=A$1)*(Schedule!B$2:B$286=A43)*(Schedule!C$2:S$286=A$2),Column(A:Q))),Row(1:1))))

Can you see what I've done wrong?
Thanks for your help! I do appreciate it very much.
Kamille

 

[Biff]

Hi!

The formula looks OK.

I created a test file the size that you mentioned:

286 rows by 19 columns.

Times start at 6:00 AM to 8:00 PM in 15 min increments for
Monday through Friday.

Randomly filled the table with "phone", "aux", and "break".

It works for my test file. No problems!

There's nothing wrong with the formula you're using. I'm
using the same EXACT formula.

Are you sure that 10:00 AM is entered as a TRUE time?

Would you like to see my test file?

Biff

 

[kamille824]

Nope, re-formated my time fields and it's working great now.
Thanks again for all your help!