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

[Urizen]

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
https://www.dropbox.com/s/ehp3qc983f3lrzj/Annual Leave Chart 2.xlsm?dl=0

 

[Rijnsent]

Hi Urizen,

that's quite a big list of questions, but let me try to give you my ideas. My main idea: you'd probably want a database for this task.

Q1 - Excel is rather bad at rights based on individual accounts. You can lock certain cells, but that means that they are locked for everybody. Some ways around this: a workbook per employee with an aggregation workbook for you/the admin, but you'd still need something to block the users from entering the file of a collegue. For that, a macro could maybe do the trick: with VBA you could e.g. check the user/login name of the person opening that file and compare it to a value (and close the file if that person is not alllowed to open that file). Even with that, you with multiple users with their own rights better off with an MS Access database.
The 18% part of Q1 -> again VBA to fix that.

Q2 - If you have a FIFO system (first request in gets granted if it can be granted), you could use VBA. VBA-Excel and multiple users is not the best combination, as it gets unstable quite fast, the next step is a small database (e.g. Access).

Q3 - a small macro that loops through all employees and updates the "Staff" list from there - so you basically have one extra list somewhere on a hidden sheet that's used as base for the validation.

Hope those pointers help,

Koen