After Hours Support Roster
THE PROBLEM
I have to prepare a weekly after hours support roster with the following criteria:
The roster changes each Wednesday
There are seven departments. In the first two, there is a primary and secondary staff member listed; the other five groups have only one person listed each week.
So far I have created an Excel workbook that has two sheets.
Sheet one ?Master? has the start date as column headers and rows with the seven departments and their staff. For the first two groups, each combination of primary and secondary has been listed, just as if they comprised an individual.
For each combination or single staff member on call there is a 1 in the corresponding name/date cell.
My second sheet is ?Weekly list? that has the start dates as column headers again.
HOW IT CURRENTLY WORKS
In the Master sheet I select a column, go to Data|Filter|Autofilter. At the column I select 1. This reduces the list of names to only those on call that week. I highlight/copy that column and paste it in the relevant column of Weekly list.
Each week I copy the coming two weeks rosters from the weekly list and email it to all involved via Lotus Notes.
WHAT I WOULD LIKE TO DO:
List each person in the first two groups only once rather than each combination they?re in (yet it important to know who is Primary and who is Secondary)
Use something similar to autofilter but that would not require me to switch off current column highlight next and then go through whole process again (autofilter can only do a column at a time).
As well as Excel and Notes, I have Word and MS Access (though don?t know much about using it).
Need to be able to edit the end result so that sudden leave etc can be handled.
Thanks