Distributing staff to different roles

[San Shan]

I have to distribute 20 staff in 6 different locations weekly. Each location
needs varying staff numbers to manage functions. Staff have to rotate each
week and the location worked should not be continued in the following week.
Please help to build a roster in excel 2003

 

[Gary''s Student]

Lets assume that 4 of the locations require 3 staff and 2 of the locations
require 4 staff. Lets number the staff from 1 to 20. In a given week we
fill cells A1 thru B20:

loc1 1
loc1 2
loc1 3
loc2 4
loc2 5
loc2 6
loc3 7
loc3 8
loc3 9
loc4 10
loc4 11
loc4 12
loc5 13
loc5 14
loc5 15
loc5 16
loc6 17
loc6 18
loc6 19
loc6 20

For the next week we take person #20 and move then to the top of the list,
pushing the others down. We repeat for persons #17, #18, and #19

In the second week we have:

loc1 17
loc1 18
loc1 19
loc2 20
loc2 1
loc2 2
loc3 3
loc3 4
loc3 5
loc4 6
loc4 7
loc4 8
loc5 9
loc5 10
loc5 11
loc5 12
loc6 13
loc6 14
loc6 15
loc6 16

Each person is now working at a different location than in the first week.

Repeat this process each week.

 

[Sam Shan]

Thank you Gary. Locations 1 & 2 required 5 staff, location 3 required 4,
location 4,5 &6 required 2 each. I took the bottom 5 staff to push it down
from top for week 2 and so on. It worked! Much appreciated.