Self Populating Rota

[davep88]

I am attempting to create a rota, for weekday and weekend duties. I would like it to self populate based on two lists of names. The following rules need to apply;
- Mon-Thurs - to automatically populate from the Senior and Junior Staff lists
- Mon-Thurs - the on-call person must alternate between a Senior and a Junior (i.e. Mon = Senior, Tues = Junior, Wed = Senior etc)
- Mon-Thurs - the Late Rota person must alternate between a Senior and a Junior (i.e. Mon = Senior, Tues = Junior, Wed = Senior etc) so that there is always a Junior and a Senior work on each day
- Each member of the Senior team needs to work the same frequency of Mon-Thurs as the rest of the Seniors. i.e. 1 in 15 weekdays
- Each member of the Junior team needs to work the same frequency of Mon-Thurs as the rest of the Juniors. i.e. 1 in 17 weekdays

- Friday - The same rules apply as above, but as the people who work on Friday are also expected to work the weekend, this rota needs to populate separately, so that all Seniors do Fridays with the same frequency as other Seniors i.e. do a 1 in 15 weekends (same for the Juniors i.e do 1 in 17 weekends)

Below I have pasted a copy of an example table in which I would like to populate the names from the right.
Thank you for your help in advance

Excel 2016 (Mac) 32 bit


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[TH]

L​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]

Week beginning​

[/TD]
[TD]

Duty​

[/TD]
[TD]

Mon (a)​

[/TD]
[TD]

Tue (b)​

[/TD]
[TD]

Wed ( c)​

[/TD]
[TD]

Thu (d)​

[/TD]
[TD][/TD]
[TD]

Fri & Weekend​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Seniors[/TD]
[TD]Juniors[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cara[/TD]
[TD]Chris[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dave[/TD]
[TD]Donald[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Euan[/TD]
[TD]Eve[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Freddy[/TD]
[TD]Fiona[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]George[/TD]
[TD]Greg[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hannah[/TD]
[TD]Harry[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Iain[/TD]
[TD]India[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jerry[/TD]
[TD]Josh[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Kerry[/TD]
[TD]Kristina[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Liam[/TD]
[TD]Lucas[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Molly[/TD]
[TD]Marty[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nick[/TD]
[TD]Naomi[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Olivia[/TD]
[TD]Oscar[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Peter[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Quentin[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

18​

[/TD]
[TD]

05/09/2016​

[/TD]
[TD]

Late duty​

[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"][/TD]
[TD][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

19​

[/TD]
[TD][/TD]
[TD]

Oncall​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

 

[bnorton4]

Paste this formula into what will be d3 after you make the below modifications =INDEX($J:$K,IFERROR(MATCH($C3+D$2-IF(AND(MOD($C3+D$2,2)=1,$C3+D$2<52),26,IF(AND(MOD($C3+D$2,2)=1,$C3+D$2<78),52,IF(AND(MOD($C3+D$2,2)=1,$C3+D$2<90),78,IF(AND(MOD($C3+D$2,2)=0,$C3+D$2<61),30,60)))),$J:$J,0),MATCH($C3+D$2,$J:$J,0)),2)
I added a helper row between rows 1 and 2. Once the new row is created I typed 1 in c2, 2 in d2, 3 in e2, 4 in f2, and 5 in h2. I also added a helper column between b and c and typed the values 0 in c2, 5 in c3, 10 in c4 (continue in increments of 5). I numbered the seniors odds starting at 1 and going to 25. I moved the Juniors beneath the Seniors in column K and gave them even numbers 2 through 30. Kind of a mess but seems to work.