Rota Rules

[dariuzthepole]

Hi Folks,

I'm devising a new dual rota at my work and I want to automate it as much as possible. We have two offices and a mixture of experienced and newer staff. The rota will consist of Primary and Secondary staff (in columns D & E) and I have put staff names into the following named ranges:

• ExpOffice1
• ExpOffice2
• NewOffice1
• NewOffice2

The rules are as follows (in relation to being on Primary and Secondary shifts at the same time):

• NewOffice1 cannot be on at the same time as NewOffice2
• ExpOffice2 cannot be on at the same time as NewOffice2

Ideally I'd like a simple Yes/No in column F as a simple check to see whether the combination of names in columns D & E satisfy these rules.

Any help will be much appreciated!

 

[RCBricker]

might needs some more info to get this correct.

Make sure I have everything right so far.

You have 4 named ranges with names based on location and experience.

Why not one data set

names in column A
and E1, E2, N1, N2 in column B

Then all you need is an IF-AND statement (or an IF- OR statement) with a vlookup or index/match imbedded to determine if a N2 is scheduled at the same time as an E2 or N1.

something like :

=IF(AND(OR(VLOOKUP(C12,A1:B9,2,0)="e2",VLOOKUP(C12,A1:B9,2,0)="n1"),VLOOKUP(D12,A1:B9,2,0)="n2"),"No","Yes")

rich