KentuckyBrian
New Member
- Joined
- Dec 26, 2016
- Messages
- 3
First of all, I apologize for the length of this post. I hope it makes sense...
I currently am responsible for rotating about 25 daily duties between 50 employees which takes me a few hours each week. I am not very good at Excel at all but have managed to improve upon the existing process my department was using prior to me transferring in by created a simple spreadsheet. The problem is I'm stuck and don't know how to make any additional improvements.
I currently have an "input" tab with drop down lists that reference employees that work particular shifts/days that have been trained for particular duties.
Duty 1, Duty 2, Duty 3
Monday drop down drop down drop down
Tuesday drop down drop down drop down
Wednesday
Thursday
This tab is easy to see a week at a glance and will highlight employee names if assign them to more than one duty on the same day by mistake. (side note: this duplicate function doesn't always work which is odd...most of the time it does but sometimes a name that has been duplicated won't get highlighted? I verify that other names in the same row get highlighted if duplicated and I check the spelling)
I also have an "output" tab that has cells referencing the data in the drop down cells in the input tab. The output tab contains some other essential information like shifts/lunches and is much better to look at. I make a copy of the output tab which then gets e-mailed out to all the employees each week. This tab would not be idea to complete the duty rotation on b/c it is much larger and you cannot see a week at a glance.
The problem is that I currently have to make a copy of a blank master spreadsheet each week to create a new schedule. Ideally, I would like to use just one spreadsheet, but I can't do that w/o losing the data on the input and output tabs when I go to make changes for the next week. The goal is to keep historical data and eventually keep a tally of how many times employees get assigned to specific duties each month/quarter etc.
I hope there is a solution to this problem that I'm not thinking of. This spreadsheet has to be pretty easy to use as eventually other people will be using it.
Thanks in advance for any suggestions!
I currently am responsible for rotating about 25 daily duties between 50 employees which takes me a few hours each week. I am not very good at Excel at all but have managed to improve upon the existing process my department was using prior to me transferring in by created a simple spreadsheet. The problem is I'm stuck and don't know how to make any additional improvements.
I currently have an "input" tab with drop down lists that reference employees that work particular shifts/days that have been trained for particular duties.
Duty 1, Duty 2, Duty 3
Monday drop down drop down drop down
Tuesday drop down drop down drop down
Wednesday
Thursday
This tab is easy to see a week at a glance and will highlight employee names if assign them to more than one duty on the same day by mistake. (side note: this duplicate function doesn't always work which is odd...most of the time it does but sometimes a name that has been duplicated won't get highlighted? I verify that other names in the same row get highlighted if duplicated and I check the spelling)
I also have an "output" tab that has cells referencing the data in the drop down cells in the input tab. The output tab contains some other essential information like shifts/lunches and is much better to look at. I make a copy of the output tab which then gets e-mailed out to all the employees each week. This tab would not be idea to complete the duty rotation on b/c it is much larger and you cannot see a week at a glance.
The problem is that I currently have to make a copy of a blank master spreadsheet each week to create a new schedule. Ideally, I would like to use just one spreadsheet, but I can't do that w/o losing the data on the input and output tabs when I go to make changes for the next week. The goal is to keep historical data and eventually keep a tally of how many times employees get assigned to specific duties each month/quarter etc.
I hope there is a solution to this problem that I'm not thinking of. This spreadsheet has to be pretty easy to use as eventually other people will be using it.
Thanks in advance for any suggestions!