Self-updating week-based schedule with coloums. Is this possible? Many questions within.

A fella at work asked me if there was a way for him to lock coloums so that the color-information he checked there was transffered as the schedule was updated and he wouldn't have to copy-paste / re-color boxes all the time. I expanded his thoughts a bit and the formula(s) I am after is supposed to do this:


Have a week-count that is updated by todays date - A NOW() function for weeks, in short.

Use this week-count as a reference in a formula that updates weeks 1-52 in single coloums.

The position of the week-count should move so that the current week is always upper-left on the coloums.

And all saved data in the space below the week-count should move along with it as the week moves.

For example - Say current week is switching from 6 to 7 and week 8 has two red and a green-filled box some spaces down(10 spaces are used by each week as standard). The function should move all weeks 'one step left' and put week 6 back at the end of the schedule, as well as move all coloration/data in week 8's coloum along.


Is this to much to hope for from Excel 2010 or can this be solved through a variety of functions and such?

Grateful for responses, thank you for your time.