Rotating list of names (for a roster) need to move in opposite direction to examples seen

[LuckyJim15]

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

[windy58]

Could you upload a sample workbook.

How many weeks are there is there?

Windy

[LuckyJim15]

Hi Windy,

Thanks for replying so soon. There will be eventually 52 worksheets (1 for each week of the year) in the
roster workbook. There is also a duty worksheet where the times for each duty is stored and a master sheet
to create a clean copy for each week (hopefully once finished this will only be used to create one-off weeks which do not fit the regular pattern such as over Christmas, etc).

I am also constrained a little bit by how things were laid out previously (and expected to look) and that
the weekly roster sheets are pinned up each week for employees to view so must be easy to fit on sheets of A4 for each week.

In my small testing spreadsheet (Roster names tester)it has a week number and a counter (to find the
position of the name in the list) these are not in the proper worksheet as I'm still stuck on trying to get each name rotate down in the tester sheet. Also on the tester sheet all the names are rotating on the same
worksheet however on the proper roster the names will have to rotate across each of the weekly worksheets.
I did this on the tester to see if my formulas worked. I have put in some test data in the worksheets to
show how they work.

I hope this is helpful.

[windy58]

Hi LuckyJim15

A couple of questions.

For this staff roster how many staff will it have in total?

Is there a set shift for each week
For example:
Week 1, a,b,c,d,e,f,g
Week 2, b,c,d,e,f,g,a

You have 204 duty no.s in your list how are they distributed?

Windy

[LuckyJim15]

Hi Windy,

No set pattern for duties in duty lines they are manually calculated at the end of the previous year to make sure all duty weeks are fair as possible and where inconsistencies are found extra days off or shorter duties are applied. So if you have a heavy week the next week will be lighter to compensate. Actual duties assigned are typed into the roster and do not generally change as rest days and cover is pre-built into the roster using spare duties. This all works well.

I just need so help rotating employee names down in the name column for the next week so in week 1 if you are on line 1 you will move down to line 2 next week, etc. If you are at the bottom duty line in week 1 you will be moved up to duty line 1 in week 2 and so on. I intend to copy the master template for all weeks but then change week 1 to have all the employee names in it and the others will look at the week before to find the relevant employee names. I keep trying to work out if I can use a counter and somehow +1 to move to the next line down. As you may have seen in the first formula I posted which rotates the wrong way it subtracts each time (I think!).

Example: Week 2 looks at week 1 for the employee names and offsets by 1 to move all names down by one. The name for the bottom duty line for week 1 then needs to be set to duty line one for week 2 and so on for each week.

hope this helps

LuckyJim

[windy58]

Really not sure how it is meant to work.

See attached.

This will move people down the list as you change the week number.

Not sure if it what you want but you may be able to do something with it.

Windy

[LuckyJim15]

Hi Windy,

Thank you for the formula. I've just had a look at it and it rotates the same way as my tester names formulas. unfortunately the names go up not down the list (same as my formulas). I've really struggled to get it to work in the direction I need it to go and still not managed it.

Thank you again for the help. Not sure how I can do it if I can't find a formula to do it. Maybe using VBA however I really didn't want to go in that direction as people using the spreadsheet keep saving it as non-macro enabled also my VBA knowledge is virtually non-existent.

LuckyJim

[windy58]

Does this mean that you want the person in position 9 to move to the top of the list and the rest to move down?

Windy