Rolling roster

**Hagar42** · 11-14-2012, 12:37 PM

Hi

I've been working on a rolling weekly roster for work and was wondering if there was a formula were when the name at the bottom of the roster would automatically move to the top.

I've attached a test roster so you may get an idea as to what i'm talking about.

On my roster person 53 would up to position 1 the following week.

Thanks in advance.

Hagar

**Kevin UK** · 11-14-2012, 12:40 PM

Why don't you post a sample workbook with dummy data instead of that pdf!!

**Hagar42** · 11-14-2012, 12:55 PM

Please find attached the non pdf version

**J.McQ** · 11-17-2012, 09:32 PM

What you can do is this:

Name column A "this week", and column B (which is blank) "next week". Then in column B put in cell B3: =A53 (which is the last person on your list in column A), then in cell B4 put =A3 and copy that down the remainder of the column. The result is that in column B, the person from the bottom of the list moves to the top, and knocks everybody else down one cell.

Then just copy and paste special (values) the contents of column B, into Column A.

Excel will then automatically do the calculation again in column B based on what is now in column A.

So effectively, column A will show the current week's roster and column B will show the following week, and all you have to do is copy and paste special column B into column A each week.

I'm assuming here that the only thing changing is the person and nothing else.

You could go one further and record it as a macro, then just run it each week to save the bother of copying and pasting column B over to Column A each week.