Use Excel to Schedule Employee Shifts

My organization has all employees fill out a blank schedule in excel and then mark their availability in the cells of the matrix listing all of the shifts.

traditionally we have matched availabilities to fill out the schedule by hand, but i'm assuming excel could automatically compile all of the availabilities from the various employees, and put together a master schedule for me...

any suggestions

Maybe if you post some sample data on how the employees fill out the sheet it might help. Is the format always consistent? Be more specific with the amount of data and how it is formatted.

here is the sample of the sheet they fill out. they typicall place an 'X' in shifts they CANNOT work, but they could easily place X's (or even type their names) in shifts that they CAN work if that would make things easier...

we typically have about 75 people turning these sheets in.

anything else you'd like to know?

thanks hoffey!

also... we have them e-mail their sheets in, so if there's a way to easily consolidate them all into a single workbook that would also help

The first thing I would do is make the file consistent each time, so each time you receive it, all the data will remain the same cells. See attached for an example.

From there you can consolidate all of this information into one workbook pretty easily, and then start to analyze who can work what time/day.

Here is a more informative version. I don't have time right now to start on the other steps, but if I have some free time tomorrow I will play around with it. Getting all the information on one spreadsheet is the easy part though.

thank you very much - any assistance you can offer will be greatly appreciated.

if it's possible to replicate for someone not too experienced with VBA and macros that would be great also, because we have a schedule for supervisors as well as tellers...

thanks again!