Formula for weekly rotation of lineup schedule

NoS

Member
Joined
Jan 17, 2013
Messages
835
Reaction score
0
Points
16
Location
British Columbia
Excel Version(s)
Excel 2010
Attempting to automate something currently done with pencil, paper and a calendar.
Have managed to eliminate the calendar using a formula but having trouble creating the lineup for any given week.
The attached workbook has what I'm starting with and the results I'm looking for.

I'm hoping for a formula in cell B4 of the "Starting Point" sheet that can be dragged to fill the rotation schedule.

The challenge I'm running into is that both the lineup and weeks are numbers but neither uses the number 13 which seems to create a moving target that I just can't hit. My formula to force 13 to 14 has eliminated the 13 but other numbers are being skipped in succeeding rows.
Your assistance would be greatly appreciated.
Thank you for your time.

NoS
 

Attachments

  • Rotation Question.xlsx
    29.9 KB · Views: 21
OK, I left this for a while then came back.
Simpler than I thought.
Couldn't stop thinking I needed to involve the original lineup in a formula, but I don't.

B4 =B$2 and drag across
B5 =IF(B4+1=13,14,IF(B4+1<42,B4+1,B4+1-41)) drag across and down

Like they say, KISS (Keep It Simple Stupid)

Thanks to any one who took the time to look at this question.

NoS
 
Back
Top