Sickness Analysis

**pearson** · 06-03-2010, 03:35 PM

Hi,

Project:
I have been referred to the guru's of this site following good reviews so hope you can help.
I have a project for the workplace regarding sickness levels, basically what i need is a spreadsheet to highlight sickness through a yearly calender display.

Help Needed
I am using a list box (form control) to display the names of the staff. Next to the source data of names which this list feeds from is sickness days that staff have taken off. I need somehow to display each day sick in red on the calender by the member of staff which was selected on the list box so when you select the next advisor from the list their sick days appear only.

I have attached the file to make more sense of the above, i am not very good with VB either. Any help much appreciated.

Many Thanks,

**masteff** · 06-04-2010, 01:29 PM

The following is based on you starting in cell C20 and copying the conditional formatting to the rest of the calendar...
=C20=INDEX(All_Staff,MATCH($G$7,INDEX(All_Staff,0,1)),MATCH(C20,INDEX(All_Staff,MATCH($G$7,INDEX(All_Staff,0,1)),0)))

Basically it finds the row for that employee and sees if that cell's date is in the row of data for that employee.

**pearson** · 06-04-2010, 07:10 PM

Thanks for your speedy reply, really appreciate you looking at this! However when i input your formula into conditional formatting into cell C20 as advised the error of "You may not use unions, intersections, or array constants for conditional formatting criteria" . Any suggestions on why this error would appear or am I doing something wrong here?
This was tested in office 2007 however it will be used within a 2003 environment if that makes any difference.
Again really appreciate it!

**masteff** · 06-07-2010, 03:56 PM

Opps, sorry, I tried to make it less ugly by using the name you'd created for your data, but then I didn't test it out myself!!! It apparently doesn't like you to use names in conditional formats.

Replace All_Staff with $AE$13:$AU$99 so:
=C20=INDEX($AE$13:$AU$99,MATCH($G$7,INDEX($AE$13:$AU$99,0,1)),MATCH(C20,INDEX($AE$13:$AU$99,MATCH($G$7,INDEX($AE$13:$AU$99,0,1)),0)))

**pearson** · 06-08-2010, 06:36 PM

You absolute genius!! I have been trying countless hours to get something to work. Thanks for the excel lesson and your time, much appreciated!!

Chris