Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Highlighting multiple triggers
I am really starting to wish I hadn’t suggested our sickness recording spreadsheet could be improved…
I am fairly confident with Excel, and can see the possibilities – and will attempt VBA if necessary (I suspect it may be but I’m not really familiar with it) I have a spreadsheet to track sickness with a summary sheet as the first worksheet and subsequent worksheets for each month. Sickness is recorded as 1, 0.5 or 0 where 1 is a full day sick, 0.5 is half day and 0 is a non-working day (some staff work shifts). The monthly sheets are set up with columns A & B holding staff names, C with a formula to display the total value from range D:AH (this being each day of the month). The summary sheet pulls values from Column C for each month, and also has the summary data from the previous year. I was intending to directly input the values from last year as the old sheet is not directly compatible. The year runs from April to March. I need to be able to flag the following 3 'triggers': Sick for 5 or more working days in a row Sick for 3 working days over the last rolling 3 month period Sick for a total of 5 or more working days over a rolling 12 month period I am thinking that the monthly worksheets could have conditional formatting where if 5 of the previous 7 cells are not blank the text turns red (some staff work weekend so they may or may not need to be included) – but due to the varying shifts and rolling requirements I am tying myself up in knots! I would really appreciate any help anyone can give me – unfortunately my ambitions don’t match my expertise – yet! Hopefully this gives enough of an idea of my aims – if not please let me know. Thank you in anticipation |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting multiple triggers
Food for thought...
You might find it easier to manage if you add 12 month columns to the right og AH, and collect monthly data there. This, of course, would require each entry to include a date so each month column could collect only its entries. Alternatively, you could continue using a 'working' sheet month to month, but use only one sheet for the current fiscal year where you collect data for each month over 12 columns. This will still provide familiar task processing but allow you to store multiple years in a single file (1 sheet per fiscal year). If you organize the headers for the month columns as 3 character abbreviations then you can store the current month on the 'working' sheet and use a macro to 'post' the data to its respective column on the fiscal year sheet, clear the 'working' sheet, and set the next month so you're ready to go. The macro can also handle the highlighting of consecutive days. -OR- I'm assuming you also want to record the actual days (on fiscal sheet) that someone is sick, and so you could also set up day rows for each month and worker columns on a single worksheet for each fiscal year. Then everything is in one place and you could use outlining with totals for each month in the top row so you can see them when the group is collapsed. I do something similar where I have a sheet set up for each day of a fiscal year on contiguous rows, and the tracking criteria in columns starting in "C". Column "A" contains formulas to automatically populate the entire sheet with dates when the fiscal start date is entered in month1. Column "B" is set up to display the 3 character abbreviation for the day of its respective value in column "A". The days are configured same as a calendar, so if your fiscal year starts April 1st then you'd enter 4-1 into the 1st cell of month1 because this year April 1st is a Sunday. If it was a Tuesday then you'd enter 4-1 in the 3rd cell of month1, leaving the previous cells blank. Feb is configured to leave the 29th cell empty if not a leap year. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
|
|||
|
|||
Thank you for taking the time to respond. I can see a couple of issues (staff work out in the field and we may not know they are off sick until some time the next month, so clearing the sheet could be problematic, and we need to collate half days as well, so a date may not work) - but I will certainly see if I can incoporate some of your concepts.
Quote:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting multiple triggers
melhay explained on 3/12/2012 :
Thank you for taking the time to respond. I can see a couple of issues (staff work out in the field and we may not know they are off sick until some time the next month, so clearing the sheet could be problematic, and we need to collate half days as well, so a date may not work) - but I will certainly see if I can incoporate some of your concepts. You could easily include a column for duration that uses hours or decimal fraction of a workday's normal shift duration. For example... Hours: 4.5 Days: (based on 8-hour shift) =8/4.5 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
|
|||
|
|||
Thanks Garry!
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
STOP Highlighting Multiple Cells | Excel Discussion (Misc queries) | |||
How do I stop the cursor from highlighting multiple cells? | Excel Discussion (Misc queries) | |||
**finding duplicates from multiple columns and highlighting** | Excel Programming | |||
Finding and highlighting duplicates across multiple worksheets | Excel Programming | |||
the curser is highlighting multiple cells instead of individuals | Excel Discussion (Misc queries) |