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 of | 7/5/2021 | 7/12/2021 | 7/19/2021 | 7/26/2021 | 8/2/2021 | 8/9/2021 | 8/16/2021 |
route 1 | driver 1 | driver 2 | driver 3 | driver 4 | driver 5 | driver 6 | driver 7 |
route 2 | driver 2 | driver 3 | driver 4 | driver 5 | driver 6 | driver 7 | driver 8 |
route 3 | driver 3 | driver 4 | driver 5 | driver 6 | driver 7 | driver 8 | driver 1 |
route 4 | driver 4 | driver 5 | driver 6 | driver 7 | driver 8 | driver 1 | driver 2 |
route 5 | driver 5 | driver 6 | driver 7 | driver 8 | driver 1 | driver 2 | driver 3 |
route 6 | driver 6 | driver 7 | driver 8 | driver 1 | driver 2 | driver 3 | driver 4 |
route 7 | driver 7 | driver 8 | driver 1 | driver 2 | driver 3 | driver 4 | driver 5 |
route 8 | driver 8 | driver 1 | driver 2 | driver 3 | driver 4 | driver 5 | driver 6 |