Schedule Date/Time & Person validation

[Chilliemark78]

Hi all

I am putting together a schedule for super user support for our EPR go live in September and I want to alert users to assigning the same super user to the same shift on the same date as seen below.  Because Data Validation will only allow one level of validation I cannot have a dropdown list and validation of name against date & time and I have tried various Countifs nested statements applied to the required cells, I must admit it's not my strong point and I would be grateful for any help and guidance you can give

[#2]

@Chilliemark78 

=IF(AND(A3="Doctor",COUNTIFS($A$3:$A$10,A3,$B$3:$B$10,B3)>=2),"Marc Williams",IF(AND(A3="Staff Nurse",COUNTIFS($A$3:$A$10,A3,$B$3:$B$10,B3)>=2),"Marion Williams",""))

Maybe with this formula which checks double entries for "Doctor" and "Staff Nurse" and their shifts. The formula can be adapted to more roles. 

[#3]

Hi Quadruple_Pawn Thank you for this, however I want to allow multiple instances of 'Role Supporting' for the same shift but prevent a name appearing twice with the same Date/Shift combination. And so, I can have Shift /Date combination of 8/9/2022 Shift 08:00-16:00 with Doctor -Mark Williams & Doctor Fred Bassett but I cannot have Shift /Date combination of 8/9/2022 Shift 08:00-16:00 with Doctor Mark Williams & Doctor Mark Williams plus I want this to apply all other shift members

[#4]

@Chilliemark78 

Unfortunately i didn't understand what you want to do when i posted my first reply in this discussion. My suggestion is to apply conditional formatting for the instances in which duplicate entries for "Role supporting" , "Shift" and Doctor/Staff Nurse/HCA are found. You can try this for example by selecting "Doctor4" in cells D2 and D3.

[#5]

Thanks very much. I will give this a go!