I am trying to create automated rota for staff names to move every week

Question

Hi. I am trying to create a rota which the names rotate every week. the job times stay the same.

Answer 1

Hello Ashraf Ali,

Thank you for reaching out in Microsoft Q&A forum.

I understand you want to set up an automated rota where staff names rotate weekly without changing the existing shift schedules.

To do this, please follow these steps:

Option 1: Rotate the Name list with one “Week Offset” cell

Step 1: Put staff names in a single list

Create a list of staff names in a column, e.g. on a sheet called Staff: Staff!A2:A12 = A, B, C, D… (your staff)

Step 2: Add a “Week Offset” cell

In a separate cell (example Control!B1), enter a number:

• 0 = this week
• 1 = rotate by 1 (next week)
• 2 = rotate by 2 (week after) …etc.

Step 3: Use this formula to display the rotated staff name per row

In your rota sheet, in the first staff row (example A2), use:

=INDEX(Staff!$A$2:$A$12, MOD(ROW()-ROW($A$2)+Control!$B$1, ROWS(Staff!$A$2:$A$12))+1)

Then fill down.

This automatically rotates names by however many weeks you set in Control!B1.

Option 2 (If you have Excel 365): Use one spill formula

If you have Microsoft 365, you can generate the full rotated list with one formula:

=LET(n, Staff!A2:A12, k, Control!B1, DROP(VSTACK(DROP(n,k), TAKE(n,k)),0))

This returns the entire rotated list at once (then your rota pulls from it).

Once your Name column is dynamic, your shift grid (Sat–Fri) can remain exactly as it is (Day Off/COVER/times). Each week, you only change the Week Offset number and the names shift automatically.

If you want the same “Day Off / Cover” colors, use Conditional Formatting rules by cell text or shift keywords.

Let me know how it goes

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.