Hello guys,

I am looking to create a leave planner for my working team which consists of about 12 members. I have attached a sample sheet of what I am trying to create Leave Test.xlsx

The idea is very simple (hopefully the solution is too :P). I have listed the team member name in the first column and their role in the second. As it is a small team and there are only two people working for a given role, I would like to ensure at least one person is present for each role on any given day.

I have made a calendar and would like to do Data Validation with three options: P for Present, L for leave and LO for Leave Override.

The idea is to have L selected only when no other employee of the same role has already marked leave. If L is already marked and the second person of the same role also has a critical requirement, LO should be an option to choose. But the criteria is this LO should be chosen only by the manager (windows NT based authentication for LO value).

I hope I am making sense. Please do look at the attachment included and let me know if any clarification is required. Looking forward to some urgent assistance.

Thank you in advance.