Employee Scheduling in Excel

[Mayrie]

My Problem:
I currently create my employee schedule in excel and I am trying to find a way to decrease the time I spend entering date. Currently worksheet A lists the employee name in the first column and the days of the week as column headers, where I input the employee shifts for the week. This view is good for the employees because it shows them their total hours for the week. But I need to make sure we have the right staffing in the different departments, we have 6 job codes. Right now I manually enter the data onto worksheet B, the set up of B is not ideal. I have the days fo the week listed as column headers and the job codes in the first column. I basically type in the shift and employee name this table to see staffing by department. This ends up taking a ridiculous amount of time.

What I Want to Do:
Worksheet A: row headers-employee name, column headers-day of the week, start and end times desplayed in table
Worksheet B: row headers-job code, column headers-day of the week, employee name and shift displayed in table
I want to be able to enter only the shift times into either table and have the other table pull the information. The problem is that I want to be able to sort my list of employees (depending on job code, shift, a to z etc) and not have it mess up the other worksheet. I thought a pivot table would work best for this, but I cannot figure out the best way to set up my worksheet to allow for all the data to be on the pivot table.

Any suggestions would be greatly appreciated!

 

[ilopata1]

I know this is an old thread but in case others should be read it, as I did, you might want to look at Labor Scheduling Spreadsheet for Microsoft® Excel - Scheduling Spreadsheet for Microsoft Excel. The free trial version would seem to do most if not all of what you were looking for and the paid version includes auto-scheduling, web publishing and a few more useful tools too.