Rotating Schedule

  • Basically I have 8 route and 8 drivers. Every week the route a driver has changes on a rotating schedule. The table below is an example of how it works. I creating a 1 page print out as an overview for the week to give to our dispatch and on this sheet i need to have who is on what route. I am trying to find a way to only display 1 week at a time.


    My idea was to use WEEKNUM() to find the week number and then use some formula to convert that to only 1-8. For example weeks week 9 would instead be week 1 and so on. There is 2 issues with that, 1) I cant figure out the math to get that working and 2) when the year starts over you would go from 4 (week 52) back to 1 which messes up the rotation.


    I am not opposed to using VBA, I just started out with formulas because i understand them a little bit better and was hoping to figure this out on my own.


    Any help is much appreciated.


    Week of7/5/20217/12/20217/19/20217/26/20218/2/20218/9/20218/16/2021
    route 1driver 1driver 2driver 3driver 4driver 5driver 6driver 7
    route 2driver 2driver 3driver 4driver 5driver 6driver 7driver 8
    route 3driver 3
    driver 4driver 5driver 6driver 7driver 8driver 1
    route 4driver 4driver 5driver 6driver 7driver 8driver 1driver 2
    route 5driver 5driver 6driver 7driver 8driver 1driver 2driver 3
    route 6driver 6driver 7driver 8driver 1driver 2driver 3driver 4
    route 7driver 7driver 8driver 1driver 2driver 3driver 4driver 5
    route 8driver 8driver 1driver 2driver 3driver 4driver 5driver 6