Staff rota

[MartyExcel]

Hi All,

Say I have a restaurant and need certain staff on different tasks each night. We try to make it so that each member of staff perform each job at some point in the week.

• I want to avoid one member of staff being in the pair that for example washes pots 3 days a week when everyone else only does it once.
• Some may only be able to perform two of the seven functions for a certain period of time (for example, Mike cut his finger and therefore should not be cooking for 3 days until it heals)
• I want to be able to override the rules by typing the function that the member of staff will be doing. For example, I caught Derek eating chips off a customers' plate and so I'll have him on pot wash all week next week.
• I need to factor in annual leave so that they aren't placed on any duties on Friday if they're due to take a long weekend.

The best way I can think of doing this is by conditional formatting the cells so that an individual cell highlights when we're missing ">0", that row has the least number for weekly total of that duty; you then go in mannually and type the letter representing the duty. How do I get a formula that does that though!?

Here's what I have...

Thank you v much in advance!

Martin