Building a Sickness Spreadsheet

[Zod]

Hi!

I'm currently building a spreadsheet of staff sickness. It's very simple - It has a form which collects the data (Team Name, Staff Member, Return Date and Sickness info.) This data is then added to a worksheet called Data

Column A4 down contains - Team
Column B4 down contains - Staff Member
Column C4 down contains - Absent Date (Date that sickness is logged)
Column D4 down contains - Reason For Sickness
Column E4 down contains - Expected Return Date.

I have another worksheet called Stats which is hopefully going to hold some statistical info based on the data I collect. So far I have a list of teams and how many individuals are off from those teams, a total amount of staff off sick and the total number of current staff.

What I would like to do is count the amount of times an individual staff member goes off sick in the last six months and if it is more than three I would like to bring up a flag of some sort - Is this possible either in VBA or in excel?

Many thanks,

Zod.

[Bob Phillips]

The easiset way IMO is to use conditional formatting.

Start by adding a new column, F4 down, with the days off to date

=NETWORKDAYS(C4,MIN(TODAY(),E4))

On the stats sheet, list all staff, then select that list
Format>Conditional formatting
Change Condition 1 to Formula Is
Add this formula

=SUMPRODUCT(--(SickLog!$B$4:$B$200=B4),SickLog!$F$4:$F$200)>3

Click format
Select vthe pattern tab
select a cell colour for highlighting
OK out

The last row 200, and the log sheet, SickLog, should be changed to your details.

.

[Zod]

Will try conditional formatting and see how I go!

Thanks!

[Zod]

When trying to conditional format on my "Names" worksheet I get a "You may not use references to other worksheets or workbooks for Conditional Formatting criteria" error - Also when I input the =NETWORKDAYS(C4,MIN(TODAY(),E4)) formula I get an output of #NAME (Could this be something to do with english style DD/MM/YY Dates?)

Thanks for help though!!
Zod.

[Bob Phillips]

Sorry, mea culpa. The old other worksheet problem.

Create Excel Names for the staff member range and the new days off count range on the SickLog spreadsheet and use these names in the SUMPRODUCT formula.

=SUMPRODUCT(--(Staff=B4),DaysOff)>3

The #NAME problem means you haven't installed the Analysis Toolpak. Go to Tools>Add-ins and check the Analysis Toolpak.

.

[Zod]

" Sorry, mea culpa. The old other worksheet problem. "

Hehe!

Will try this out.

Many thanks!

Zod.