Automatic rota calculator

Hello all,

I have a tricky problem. I am writing a rota for my work colleagues (doctors). I have generated a calculator that allocated the amount of on-call each colleague has to do - in terms of weekdays and weekends.

Each colleague is given a number of weekdays and weekends to work in a given time period (1 year).

My problem is how to put this into the rota in an automated way - allowing me to then change the allocated numbers, with the rota automatically updating itself. I'm sure this is possible, but I can't quite work out how to do it. The main problem I have is working out how to spread the shifts equally throughout the year.

I have attached a dummy rota (much simplified compared with the real thing). Sheet one is the number of shifts for each member of the rota. Sheet 2 is a blank rota which I need to populate.

Any help would be much appreciated!!

Many thanks,
Adrian