Hi,

I'm new to the forum so please forgive me if I have posted this in the wrong place. I am working on a roster which staff are given set duty lines for the week. Each week staff are then moved down to the next week in the roster.

Example: This is not the true roster but a simplified version. The roster duty week lines are numbered from 1 to 150, each person starts on a number (e.g: Alan starts on line 1) and each weekly roster is on its own worksheet so the example below is for the first week of the year. The next week all the names move down by 1 place and the last name at 150 moves back up to the top to line 1.

I have tried various ways to solve this but the best I can do by looking on the web and testing things out is to get the list go in the opposite direction. This was due to looking at various solutions and trying to tailor it for my situation.

The best I've managed so for is by using the following formula in each name cell

=INDEX($B$3:$B$11,IF(D3>D$13,D3-D$13,E$1+D3-1))

Currently I'm testing this out on a single worksheet with a made up list of names without duties attached as this is the area I'm focused on at the moment. So the B range is the first week list of names, D3 is the first name in the next week, D13 is a count to the end of the list, E1 is the week number (week 1, week 2, etc). This works in the opposite direction I want it to go (names move up the list and line 1 name goes to the bottom line (in this example line 9). The count to end simply is the number of rows minus the week number (this should work as there are more duty rows than weeks in the year). The hope is to simply make the first week(with data) then create the other week worksheets which is automatically populated by the formulas.

Once sorted I need it to look at the previous weeks list and move the names down to the next number of the duty lines (e.g: if on line 1 move down to line 2, etc, if at bottom line move to line 1). Each week will be on its own worksheet labelled as the first day of the week (e.g: 04-01-2016).

Date: 04/01/2016 Week: 1

Duty wk Name Mon Tues Wed Thurs Fri Sat Sun
1 Alan 1 12 3 4 5 Rd Rd
2 Ben 8 Rd Rd 9 11 104 301
3 Charlie 111 23 1 8 Rd Rd 12

9 count to end

Above is a simple example how each roster week will look. The real roster is much bigger in scale.

Can anyone help me with moving names down the list in a rotating roster please. The duty lines stay the same each week its just the names that need to cycle down. As I mentioned before I have seen rotating rosters that move up but I can't get it to move down the list.

Any help or advise would be gratefully received