Annual Leave Database - A-Z Sorting, Repeated Lists and User Access

Hi all

I have been asked to make a chart for staff in an office. Currently there are approximately 111 members of staff, however this is likely to increase in the coming months. I have prepared a workbook which has sheets for 2019 and 2020. Each of these sheets has the days of the year across the x axis and the names of staff members (inc. their team number) on the y axis.

In the body of the 2019 and 2020 sheets, there are dropdown boxes which allow users to select if some form of leave applies e.g. AL is Annual Leave, BH is Bank Holiday, etc.

Note that: The maximum amount of staff we can have off in a single day is 18% for annual leave. This is labelled at the top of the chart in the '% OFF (AL)' box. Also, a 'HALF' value means that someone will be taking a half-day off as Annual Leave. A standard day for one person in our office is equatable to 7.4 hours.

Question 1

Currently, these sheets work well enough. However I need to make the sheets accessible to all members of staff and (ideally) they will be able to input their own Annual Leave by altering the dropdown boxes. However, there are two conditions to this. Firstly, they must only be able to adjust their own annual leave. In other words, Darlena Marcinko must only be allowed to alter the boxes on her row and not (for instance) Emerson's. Secondly, they must not be able to input 'AL' or 'HALF' if the '% OFF (AL)' would go above 18% as a result. How can I achieve this?

Question 2

The 'First Refusal' sheet is designed so that, if a member of staff saw a day was at or above 18%, they could store the details of their request in case someone withdraws their annual leave. Currently this only works if they input one day as annual leave. However I would like it if I could have a 'From' and 'To' box which checks to see if any dates within that period are free. Ideally this would also automatically grant that person the leave they wanted and update the relevant sheet (2019 or 2020) as appropriate, perhaps also with a message box stating which dates were granted. How can I achieve this?

Question 3

The 'First Refusal' sheet has a field for Staff Name. I would like this to be a dropdown list of all staff members, but sorted A-Z without affecting the data in the other sheets. I find that using a paste link system dramatically increases the size of my file however and makes it much slower to run. How can I achieve this without the dramatic increase in size/ processing time?

Last Note

Thank you for taking the time to read this. If you believe that a simpler solution, such as master and slave sheets, etc exists, please let me know. I am quite new to Excel and doing my best to make this a good product for use in our office.

Thank you!
Urizen

Annual Leave Chart