I run multiple fantasy baseball and football leagues and we use an auction process (fake money) to determine the players that each team comes away with. The way the auction works is a member of the league calls out a player's name to be bid on and then the bidding war begins. The order to call out players is pre-determined and we stay in that order for the entire process of the auction. When you reach your roster limit, you no longer participate in calling out names.
I have set up a complex spreadsheet that tracks the auction for everyone in the league and I have the main tab display who's turn it is to call out a player's name to keep things running smoothly. While everyone still needs to fill their rosters it's no problem for me to set up a formula so the correct 'next team up' will appear in the desired cell. The issue I'm having is when one (or multiple) people fill up their roster. At this point they are no longer a part of the auction and will not be calling out any names. I can't get an excel formula to go to the next value.
So for example...Let's say the order is:
1. Neal
2. Matt
3. Ron
4. Justin
5. Craig
6. Jimmy
7. Ken
8. Josh
9. Hank
10. Dan
11. Jeff
12. Paul
Each person needs to fill out a 27 man roster. After the auction progresses let's say Matt calls out a player and Ron wins the bidding war completing his 27 man roster. Instead of Ron coming up as the next pick I want it to go right to Justin. And I want Ron permanently out of the order from that point through the remainder of the draft.
Keep in mind that I am keeping track of the total number of players by team and will know exactly when each owner gets to the 27 man limit. I just can't work out any type of formula that will always take me to the next person on my list that has not, to that point, reached the 27 player limit. I've set up different complex grids to try and make this work...and everytime I feel like I'm getting close, something causes it not to work.
I'd prefer to do this with a formula and not run a Macro. If anyone has any suggestions, I'd be extremely grateful.
I have set up a complex spreadsheet that tracks the auction for everyone in the league and I have the main tab display who's turn it is to call out a player's name to keep things running smoothly. While everyone still needs to fill their rosters it's no problem for me to set up a formula so the correct 'next team up' will appear in the desired cell. The issue I'm having is when one (or multiple) people fill up their roster. At this point they are no longer a part of the auction and will not be calling out any names. I can't get an excel formula to go to the next value.
So for example...Let's say the order is:
1. Neal
2. Matt
3. Ron
4. Justin
5. Craig
6. Jimmy
7. Ken
8. Josh
9. Hank
10. Dan
11. Jeff
12. Paul
Each person needs to fill out a 27 man roster. After the auction progresses let's say Matt calls out a player and Ron wins the bidding war completing his 27 man roster. Instead of Ron coming up as the next pick I want it to go right to Justin. And I want Ron permanently out of the order from that point through the remainder of the draft.
Keep in mind that I am keeping track of the total number of players by team and will know exactly when each owner gets to the 27 man limit. I just can't work out any type of formula that will always take me to the next person on my list that has not, to that point, reached the 27 player limit. I've set up different complex grids to try and make this work...and everytime I feel like I'm getting close, something causes it not to work.
I'd prefer to do this with a formula and not run a Macro. If anyone has any suggestions, I'd be extremely grateful.