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.
|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|