Hi,
I have 2 sheets in my workbook.
One that is to be the master and hold the 20 teams that are in the premier league. The order of the teams will never change, see below:
<TBODY>
</TBODY>
On another tab I have the fixtures for the season of when they play each other.
It looks like the below:
<TBODY>
</TBODY>
What I am trying to do is in the main sheet, look up week by week, who is playing who and wether it is a home or away game.
I cannot work out a formula to do this currently.
Does anyone have any ideas?
There is a posibility that games will get postponed, so I need to ability to go into the fixtures tab and move games about and it still work.
Secondly, it will be used as a point tracking spreadsheet where as you can see at the top the teams at the top start with 2000, then every place below them has 100 less.
Each match a team will risk 10% of their total points that they have. Example being, if Manchester City play Newcastle United in the first round of matches MC will risk 200 points, Newcastle will risk 110 points. If Manchester City win they get the 200 points they risked and Newcastles 110 points, if Newcastle win they get MC points plus theirs. If its a draw then the points get split 50/50 - in this case MC would get 155 and Newcastle would get 155 back.
Assuming Manchester City win, they would then be on 2310 points at the start of the next round of games and Newcastle would be on 990.
I am not precious about the layout at all.
Any questions let me know.
I have 2 sheets in my workbook.
One that is to be the master and hold the 20 teams that are in the premier league. The order of the teams will never change, see below:
| TEAM</SPAN> | Points @ start</SPAN> | Points to Risk</SPAN> | Opponent</SPAN> | Opponent Pints to Risk</SPAN> | Home/Away</SPAN> | |
| 1</SPAN> | Manchester City</SPAN> | 2000</SPAN> | 200</SPAN> | Newcastle United</SPAN> | Away</SPAN> | |
| 2</SPAN> | Liverpool</SPAN> | 1900</SPAN> | 190</SPAN> | |||
| 3</SPAN> | Chelsea</SPAN> | 1800</SPAN> | 180</SPAN> | |||
| 4</SPAN> | Arsenal</SPAN> | 1700</SPAN> | 170</SPAN> | |||
| 5</SPAN> | Everton</SPAN> | 1600</SPAN> | 160</SPAN> | |||
| 6</SPAN> | Tottenham Hotspur</SPAN> | 1500</SPAN> | 150</SPAN> | |||
| 7</SPAN> | Manchester United</SPAN> | 1400</SPAN> | 140</SPAN> | |||
| 8</SPAN> | Southampton</SPAN> | 1300</SPAN> | 130</SPAN> | |||
| 9</SPAN> | Stoke City</SPAN> | 1200</SPAN> | 120</SPAN> | |||
| 10</SPAN> | Newcastle United</SPAN> | 1100</SPAN> | 110</SPAN> | Manchester City</SPAN> | Home</SPAN> | |
| 11</SPAN> | Crystal Palace</SPAN> | 1000</SPAN> | 100</SPAN> | |||
| 12</SPAN> | Swansea City</SPAN> | 900</SPAN> | 90</SPAN> | |||
| 13</SPAN> | West Ham United</SPAN> | 800</SPAN> | 80</SPAN> | |||
| 14</SPAN> | Sunderland</SPAN> | 700</SPAN> | 70</SPAN> | |||
| 15</SPAN> | Aston Villa</SPAN> | 600</SPAN> | 60</SPAN> | |||
| 16</SPAN> | Hull City</SPAN> | 500</SPAN> | 50</SPAN> | |||
| 17</SPAN> | West Bromwich Albion</SPAN> | 400</SPAN> | 40</SPAN> | |||
| 18</SPAN> | Leicester City</SPAN> | 300</SPAN> | 30</SPAN> | |||
| 19</SPAN> | Burnley</SPAN> | 200</SPAN> | 20</SPAN> | |||
| 20</SPAN> | Queens Park Rangers</SPAN> | 100</SPAN> | 10</SPAN> |
<TBODY>
</TBODY>
On another tab I have the fixtures for the season of when they play each other.
It looks like the below:
| Week</SPAN> | Date</SPAN> | Home</SPAN> | Away</SPAN> |
| 33</SPAN> | 16-Aug</SPAN> | Manchester United</SPAN> | Swansea</SPAN> |
| 33</SPAN> | 16-Aug</SPAN> | Leicester</SPAN> | Everton</SPAN> |
| 33</SPAN> | 16-Aug</SPAN> | QPR</SPAN> | Hull City</SPAN> |
| 33</SPAN> | 16-Aug</SPAN> | Stoke City</SPAN> | Aston Villa</SPAN> |
| 33</SPAN> | 16-Aug</SPAN> | West Brom</SPAN> | Sunderland</SPAN> |
| 33</SPAN> | 16-Aug</SPAN> | West Ham</SPAN> | Tottenham</SPAN> |
| 33</SPAN> | 16-Aug</SPAN> | Arsenal</SPAN> | Crystal Palace</SPAN> |
| 34</SPAN> | 17-Aug</SPAN> | Liverpool</SPAN> | Southampton</SPAN> |
| 34</SPAN> | 17-Aug</SPAN> | Newcastle Utd</SPAN> | Manchester City</SPAN> |
| 34</SPAN> | 18-Aug</SPAN> | Burnley</SPAN> | Chelsea</SPAN> |
| 34</SPAN> | 23-Aug</SPAN> | Aston Villa</SPAN> | Newcastle Utd</SPAN> |
| 34</SPAN> | 23-Aug</SPAN> | Chelsea</SPAN> | Leicester</SPAN> |
| 34</SPAN> | 23-Aug</SPAN> | Crystal Palace</SPAN> | West Ham</SPAN> |
| 34</SPAN> | 23-Aug</SPAN> | Southampton</SPAN> | West Brom</SPAN> |
| 34</SPAN> | 23-Aug</SPAN> | Swansea</SPAN> | Burnley</SPAN> |
| 34</SPAN> | 23-Aug</SPAN> | Everton</SPAN> | Arsenal</SPAN> |
| 35</SPAN> | 24-Aug</SPAN> | Hull City</SPAN> | Stoke City</SPAN> |
| 35</SPAN> | 24-Aug</SPAN> | Tottenham</SPAN> | QPR</SPAN> |
| 35</SPAN> | 24-Aug</SPAN> | Sunderland</SPAN> | Manchester United</SPAN> |
| 35</SPAN> | 25-Aug</SPAN> | Manchester City</SPAN> | Liverpool</SPAN> |
| 35</SPAN> | 30-Aug</SPAN> | Burnley</SPAN> | Manchester United</SPAN> |
<TBODY>
</TBODY>
What I am trying to do is in the main sheet, look up week by week, who is playing who and wether it is a home or away game.
I cannot work out a formula to do this currently.
Does anyone have any ideas?
There is a posibility that games will get postponed, so I need to ability to go into the fixtures tab and move games about and it still work.
Secondly, it will be used as a point tracking spreadsheet where as you can see at the top the teams at the top start with 2000, then every place below them has 100 less.
Each match a team will risk 10% of their total points that they have. Example being, if Manchester City play Newcastle United in the first round of matches MC will risk 200 points, Newcastle will risk 110 points. If Manchester City win they get the 200 points they risked and Newcastles 110 points, if Newcastle win they get MC points plus theirs. If its a draw then the points get split 50/50 - in this case MC would get 155 and Newcastle would get 155 back.
Assuming Manchester City win, they would then be on 2310 points at the start of the next round of games and Newcastle would be on 990.
I am not precious about the layout at all.
Any questions let me know.
