I am trying to create a worksheet in Excel 2010 where people are to be put into teams - could be 3 to a team one week and then 2 the following week(maximum number of players to a team will never exceed 4ppl). Team sizes will be wholly dependant on the number of people (some weeks there may be an uneven number of players ie 21 players but it is a 2 player per team round), but it is rare or at least a very long night if we have more than 18 teams. The number of teams and players to a team will also vary on a weekly basis and to add to my joy there are rules in which I need to ensure that no two team members have played together in the previous 6 months and that only one female to a team.
I have created a worksheet (Membership), that will have the members details listed so that I can identify males/females (Column E). On another worksheet (MainPage) which is to be the front page in which the people’s names will be entered in manually(cells C8:C31, H8:H31) and on the same worksheet the number of players to a team will be entered (cell O3).
What I was hoping to do was somehow lookup the list of names and based on the number entered in Cell O3 (No of players to team) of the Mainpage worksheet, allocate players to teams which are then listed on Mainpage worksheet in cells M7 through to P24
M7:P7= Team 1 Player 1:Player 4,
M8:P8= Team 2 Player 1:Player 4 etc,
And then the data also copied across to another worksheet (TeamsHistory) which will record the date and team members for reference to ensure I can meet the rule of members not playing together in previous 6 months.
I know this is a big ask and I thank you all for the help in advance but silly me offered to assist with my husband’s darts club with entering the electronic age before I realized the logistics of what I was in for. (I know some of you may have been wondering what kind of teams I was organizing) most of the stuff that I need is pretty standard but as for writing the code for this to happen is way beyond my intelligence.


Again thank you for any help you are able to give me

Eages