Soccer Rotation forumla

[dpwilson]

I am coaching a youth soccer team. We have 6 positions on the field: Goalkeeper, 2 fullbacks and 3 fwds. The game is split in to 4 quarters with a stopping point halfway between each quarter to substitute players. The Goalkeeper will play the entire quarter with the other positions rotating at each quarter and halfway point in the quarter.

I have 9 kids on the roster and am looking for a formula which will rotate the field players evenly on every rotation as well as the goalkeeper staying in for two consecutive rotations then getting back into normal rotation after they have played this position. SO it should always have 3 players on the bench and never sitting out more than one "half quarter" at a time.

Example:
1st quarter: player 1-GK, 2-LF, 3-RF, 4-RW, 5-C, 6-LW.
Midway of 1st: 1-GK, 7-LF, 8-RF, 9-RW, 3-C,4-LW
2nd quarter: 2-GK, 5-LF, 6-RF,.............


If possible, it would also be able to change each game, so that the same three players dont start out on bench every game.
Also, there will be games when all 9 players do not show up, so it will have to be based on players who show up for each game.

As a bonus, would it be possible to create a sheet as shown here soccer rotation | Flickr - Photo Sharing! (with 8 fields) and have it poplulated with the data created?

Any and all help is greatly appreciated. Thanks in advance.

 

[jamtay317]

1.my first question is VBA Allowed?

 

[dpwilson]

1.my first question is VBA Allowed?

Its on my personal computer. I am just really getting into this excel, so Im guessing that I can enable it?

 

[jamtay317]

ok Ill work on it,

lets get this straight, all kids must play all positions? how many games are there?

 

[dpwilson]

ok Ill work on it,

lets get this straight, all kids must play all positions? how many games are there?

Yes all kids rotate through all positions. We have 16 games as of now. Thanks for the interest and help.

 

[jamtay317]

I'm having a hard time with this, Ill think about it and post on here my thoughts tomorrow. sorry

 

[jamtay317]

this is what I came up with, you can download it and use it to how if you would like
https://drive.google.com/?tab=wo&authuser=0#recent
this is the whole excel file that I created for this

 

[dpwilson]

When I click the link, it takes me to my google drive. Sorry to be troublesome, I am not really familiar with drive. Is there a way to retrieve it from there or is it a bad link?

 

[jamtay317]

I think that it was a bad link, here is a good link. This is to dropbox all you have to do is click on the link and download the file. when you open it up you will have to enable macros. then type the kids names where it says Please Type Kids Names here. and click the button that I've added
https://www.dropbox.com/sh/9sl2jbsnw6c44fn/kxSr1qFyTP

 

[dpwilson]

Thanks for the help. I will play around with it in the next day or so and get back to ya and let ya know how it worked out. Sure appreciate it.