excel logic problem

[Volly]

I'm stumped as how to lay out this logic problem. I hope I can explain this so somebody can show me a formula set to use in Excel to solve this one.

I'm working on a scheduling grid. I have 4 teams:
WAS
DAL
NYG
PHL

In week one I have 3 teams playing and 1 team idle. The next week I have another team idle and 3 playing. The grid looks like this example for 2015: (snippet of full grid - all 32 teams are in columns by division)

​

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Year[/TD]
[TD="align: center"]Week #[/TD]
[TD="align: center"]PHL[/TD]
[TD="align: center"]WAS[/TD]
[TD="align: center"]NYG[/TD]
[TD="align: center"]DAL[/TD]
[TD="align: center"]playoffs[/TD]
[TD="align: center"]Team[/TD]
[/TR]
[TR]
[TD="align: center"]2015[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]AFC champ[/TD]
[TD="align: center"]DEN[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]AFC runner up[/TD]
[TD="align: center"]TEN[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]NFC Champ[/TD]
[TD="align: center"]DAL[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]NFC runner up[/TD]
[TD="align: center"]WAS[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Superbowl Champ[/TD]
[TD="align: center"]DEN[/TD]
[/TR]
</tbody>[/TABLE]

​

​

Each year I want to change who is off on week 1. My thinking is I simply change the column header that has the team listed. So in the above example on week 1 I have PHL off. Next year I want to have WAS off on week 1. I will have a pick list that lets you select the year. It will be future dated with a list 2015 to 2040.

I will also have two pick list that has all my teams in each conference. It will show who played in the championship game.
I will also have two pick list that show the runner up in the conference championship game.
I will have one list that shows who won the Superbowl

My first problem is this: If for the the conference championship I have DAL listed, and that year has DAL off on week 1, I need to change it so DAL plays on week one and another team is off.

My second problem is if the team that is off that is the loser in the conference championship game (In the above example WAS), they need to play on week one.

My third problem is if the conference championship game loser and league champion are two of the four teams, I have to make sure they both play on week one. (The above example shows DAL and WAS)

My brain hurts trying to figure this one out. I only want to do this with formulas and no VBA script. This should be possible but I can't figure it out. HELP!