Creating a Rotating Roster

Originally Posted by scooooter

Hi, I am trying to put an excel spreadsheet together to automate a rotating roster but will need to put some constraints in such as a certain function can't be rostered if it already has been in the last week or after another certain function. Does anyone have any formulas that could help that I could play with or a tutorial of where I could find this sort of stuff? Thanks

Hi,

Theres not much to go on, how are 'functions' rostered? - did you mean to roster the function, or to roster the staff to perform it.

perhaps http://www.excelforum.com/showthread.php?t=584304 will give you a start in ideas, and when you have a more clear definition for your requirement then further, more specific, help can be given.

hth
---

Originally Posted by scooooter

Hi,

The link is along the same lines and i'll have a play round with it. Thanks for that. It's to roster the staff to perform it. Basically there is 8 different functions and 14 staff. I'm trying to set up a spreadsheet which will assign staff to functions on a daily rotating basis. But also have the ability to change it if a staff member is absent during the week without losing the information from the previous days. How can I set something so for example staff can't be on function 1 followed by function 2? And by using the formula you've provided, to manually assign staff would I put them as if they were on holiday so not to be included in selected at random?

for staff on holiday, put a z in front of their name in column A, the system then ignores them.

I guess the easy way for what you want is either to allocate both sets of staff from the same table, or use conditional formatting.

For the latter, you would need to be able to retain the first allocations and re-allocate the second until no matches were there, and presuming that you do not know VB code, see the attached.


should be self-explanatory.

hth
---

amended
The attachment is amended, the new version blanks the next line after the current allocation.
a required feature under the circumstances.
---

Originally Posted by scooooter

Thanks, that's really helpful! I just have a question which i'm unsure of how to do. How can I input the conditions? I don't know much about conditional formatting and have had a look on the net but am unsure how to tailor it into this.

Hi,

select the area that you want the condition to apply. then (note which cell is active - the odd coloured one) Format, Conditional format, and enter Formula as it would apply to the odd cell

=COUNTIF(B$8:B$24,D8)>0

(ie, D8 for that)

then set a colour pattern etc.

hth
---