Ridgleye
I sat down and did a roster for my wife's dental practice a few years ago. It was written and driven by formulas at the time as I didn't know VBA. It became a monster and the only spreahdsheet I ever wrote that started to nudge the IV right hand extremity! But it works it infinitely better than doing this job maually. Basically it uses validation to schedule only people who are available for a particular time slot and conditional formatting to check several rules such as too many nurses are being rostered for the no of dentists working and if you have gone over the time where a meal break is due or overtime kicked in. Formulas then also provided converted the graphic view to a start /finish /lunch break / roster the staff can be issued so they know their times and hours for the fortnight.
I didn't find any templates or resources on the net at the time - so I just sat down and did it and pieced together from John Walkenbach books, his site, Chandoo's site, OzGrid, Mr Excel and others each bit. Fantastic learning exercise as you are saying.
Now that I am up the learning curve on VBA I think I would do the validation part using VBA. The problem I foud is I had to find code to disable the drag, copy, cut, paste as users could easily overwrite the validation set for each timeslot. Also the conditional formatting is limited to 3 conditions in Excel 2003 so VBA could provide as many as you want - because this is a scheduling task usig colour and conditional formatting to help optimise the situation is important.
As far as structure:
I have a colourful graphical style summary sheet that acts as the roster form showing each functional area to be covered one week at a time where initials of staff are scheduled against timeslots of 30mins in each surgery from day start to end as well as a code for their breaks. Because I have used validation, there is a dropdown for each time slot showing only who is available in that time slot. This sheet has the conditional formatting and formularised flags (eg shos overtime, shows duplication of scheduling, shows the person has gone over their max hours, or is under their min hours) to help the user do the scheduling. I did some basic VBA to copy from one week to the next which then is tweeked to the issues related to that week.
A seperate summary worksheet then converts this data to tabula start/finish/break timings and totals for the week and fortnight by day by staff member.
Another sheet using the camera tool cuts up the summary sheet into a page size view that can be printed for staff and the noticeboard.
A control sheet defines the codes used, each person's avilability across each 30 min time slots Mon - Fri, their MAX and MIN fortnightly hours, lunch break durations
Another summary sheet has the data condensed so it can be exported to my payroll calculation workbook and used to compare roster to actual times and focus on differences.
It was a huge but immensely satisfying job and the improvement in efficiency and elimination of costly mistakes made it all worthwhile. I now know a lot more about VBA now and will need to write Version 2 one day soon but the original works well and does the job.
Good luck!
John
