staff schedule in excel

[Guest]

I am attempting to build...or reinvent the wheel, a scheduling spreadsheet in
excel.
My parameters are
Employee Name
Date of the Month
Date of the Week
Days
Eve
Nights
I would like to be able to input a persons name and state are they
aviablable to work a particular day on a particular shift. I have been having
no luck with filters or reports.

A B C D E
F
Name Date Dayoftheweek Day EVE
Night
Bill 22May Sun N Y
N
Mary 23May Mon Y N
N

I would like to have a report that would then filter out the day, the
person, and the shift avialable.

Any thoughts?
Lonny

 

[Biff]

Hi!

Here's a quick method. Might sound like a lot to go through but it only
takes a few mins. I'm assuming that some employees will not be available on
certain days and will therefor have N's for each catagory.

Assume your table is in the range A1:Fn.

In G1 enter this column header: Available

In G2 enter this formula and copy down as needed:

=COUNTIF(D2:F2,"Y")>0

In I1 put in a Data Validation drop down list and use these 2 entries:

Filter
Un-Filter

Select the range A2:Cn

Goto Format>Conditional Formatting
Formula is: =AND($I$1="filter",COUNTIF($D2:$F2,"Y")=0)
Set the font color to be the same as the background color
Ok out

Select the range D2:Fn

Goto Format>Conditional Formatting
Formula is: =AND($I$1="filter",D2="N")
Set the font color to be the same as the background color
OK out

Now apply an Auto Filter.
From the data validation drop down in cell I1 select Filter.
Then using the Auto Filter, filter on column G (Available) TRUE.

The CF will hide all the unwanted text and the Auto Filter will collapse the
list.

Biff