This should help but will involve a complicated formula and a macro.
To start, let's enter the year in cell B2. You can even change the font &
color of that cell so you know it is an input field.
We'll also list all staff members in column A beginning with A5 (I assume
you'll want a title and a header of some sort in the first few rows). B5
will begin listing the dates which start each week and go down 52 rows to the
end of the year. I have used Monday as the start date for each week. C5
through C56 will then hold your on-call assignments.
Okay, so in B5 we'll have the first Monday for the week containing 1/1 of
the year in B2. B5 needs this formula (sorry, I don't understand the
formula, it came from MVP Ron de Bruin in the Netherlands, so I don't know
how to change it from Mondays to another day)
=IF(ISERROR(DATE(1900,1,1)),IF(B2=1904,DATE(1904,1,4),DATE(B2,1,0)-MOD(DATE(B2,1,0)-2,7)+(7*(MOD(DATE(B2,1,0)-2,7)>3))+1),IF(B2=1900,DATE(1900,1,1),DATE(B2,1,1)-MOD(DATE(B2,1,1)-2,7)+(7*(MOD(DATE(B2,1,1)-2,7)>3))))
Then select B6:B56 and type in this formula:
=B5+7
and commit using Ctrl+Enter.
Now B5:B56 should show the Monday which starts a new week in chronological
order for 52 weeks, and A5:A12 should have your staff member names or codes
in the order you'd like them to be. Now comes the macro.
Right-click the sheet tab and select "View Code". What we want is a macro
which will update the on-call list when you change the year or add/remove an
employee. The macro will reevaluate the entire year every time, so it's not
"smart" enough to only reassign the remainder of the year if there's a staff
change, and it does not accommodate handling people who are not available for
a particular week...but for the most part it should do what you want. Here's
what you'll paste into the VBA Editor which popped up on your screen:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim staff As Long
Dim i As Long, j As Integer
staff = Cells(Rows.Count, 1).End(xlUp).Row
j = 5
For i = 5 To 56
Cells(i, "C").Value = Cells(j, "A").Value
If j + 1 > staff Then j = 5 Else j = j + 1
Next i
End Sub
Just close the window or press Alt+Q to exit the VBA Editor.
Now you have an on-call list which assigns each staff member an on-call week
in the same order throughout the year, and whenever you add or remove a staff
member or change the year in B2, the entire table will reset. If it's the
same year and you need to either add or remove an employee, you can tweak the
macro at this line:
For i = 5 To 56
so that instead of starting in row 5 perhaps it starts at the week you
really want (say the week shown in row 28)...all you'd do is change the line
above so that 5 is 28. Now go back to your sheet and change your staff list
accordingly and it will only reassign the on-call list beginning with the
week you instructed it to. Just don't forget to change the macro back to 5
when you're done.
Hope this helps.
