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

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​	Week beginning​	Duty​	Mon (a)​	Tue (b)​	Wed ( c)​	Thu (d)​		Fri & Weekend​
2​	05/09/2016​	Late duty​									Seniors	Juniors
3​		Oncall​									Cara	Chris
4​	05/09/2016​	Late duty​									Dave	Donald
5​		Oncall​									Euan	Eve
6​	05/09/2016​	Late duty​									Freddy	Fiona
7​		Oncall​									George	Greg
8​	05/09/2016​	Late duty​									Hannah	Harry
9​		Oncall​									Iain	India
10​	05/09/2016​	Late duty​									Jerry	Josh
11​		Oncall​									Kerry	Kristina
12​	05/09/2016​	Late duty​									Liam	Lucas
13​		Oncall​									Molly	Marty
14​	05/09/2016​	Late duty​									Nick	Naomi
15​		Oncall​									Olivia	Oscar
16​	05/09/2016​	Late duty​										Peter
17​		Oncall​										Quentin
18​	05/09/2016​	Late duty​
19​		Oncall​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[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.