Need advice for improving a scheduling spreadsheet

[KentuckyBrian]

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!

 

[easy2understandexcel]

Kentucky,
You can make a table that has columns for date, and everything else you want to keep historically, then setup the reports to be poulated from that central table.
I created a pretty advanced workplace scheduler very similar to what youre describing and thats what allowed me to keep historical records.

 

[KentuckyBrian]

Thank you for your suggestion. I've never created a table before but at least this gives me a direction to go in.

You wanna sell me a blank copy of your scheduler?

 

[easy2understandexcel]

Thank you for your suggestion. I've never created a table before but at least this gives me a direction to go in.

You wanna sell me a blank copy of your scheduler?

But then you wouldnt learn anything...
By "table" I just mean storing the data in an excel sheet, its nothing fancy. You just keep adding data to that sheet with a date column, and then the reports use that data to generate the schedule... so instead of 100 different schedule files, you have one.

 

[KentuckyBrian]

Sorry, keep in mind I am fairly Excel illiterate =) I'm not understanding what you mean by storing the data. On my input tab, I have to change it weekly in order for it to feed into my output tab. How do I get around this?