Mar 16 2022 06:34 AM
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
Mar 16 2022 07:05 AM
=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.
Mar 16 2022 09:03 AM
Mar 17 2022 08:55 AM
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.
Mar 17 2022 09:19 AM