I am not an expert at Excel (casual use for a few years making simple reports) but I've taken on a task of creating a spreadsheet showing the game schedule for teams in our club.
This spreadsheet is a tab in a workbook that keeps track of scores and stats. I'll describe the best I can.
We have a different number of teams per month, ie month 1 might have 9 teams, month 2 might have 20 teams, etc. Team names are all listed in another tab so these formulas can reference another tab to find out how many teams are registered for this month.
Each team only pays 5 games therefore not all teams play all other teams.
So there will always be 5 columns to generate but not always the same number of rows.
One catch is, if team 1 plays team 3 then the row for team 3 must have team 1 in it.
Each team only plays another team once.
This is an example of monthly schedule with 8 teams.
Team 1 plays Team 2 Team 3 Team 4 Team 5 Team 6
Team 2 " Team 1 Team 4 Team 5 Team 7 Team 8
Team 3 " Team 4 Team 8 Team 1 Team 6 Team 7
Team 4 " Team 1 Team 3 Team 2 Team 6 Team 8
Team 5 " Team 1 Team 2 Team 8 Team 6 Team 7
Team 6 " Team 1 Team 3 Team 4 Team 5 Team 7
Team 7 " Team 2 Team 3 Team 5 Team 6 Team 8
Team 8 " Team 2 Team 3 Team 4 Team 5 Team 7
As you can see it doesn't matter which teams play against each other as long as each team gets 5 games so the more random the better.
Next time, about a month later when I publish the next schedule, there will be a different number of teams that will listed in another tab.
I'd really appreciate it if there was a method of generating this schedule each month so I don't have figure it out each time.
Thanks in advance for any responses. I've puzzled through this for a couple of weeks now.