Logical Sequencing Problem

[SanJose]

I have a spreadsheet we use to display our Fantasy Football auction on a big screen in a conference room. Ten guys take turns 'nominating' a player, who can then be 'bought' by anyone. My sheet lists every nomination, 1-160, going through a logical sequence (Team A, Team B, Team C, Team D.. repeat).

The issue is, that once a team has drafted 16 players, he is out of the auction - when that happens, we skip that team and the next team gets to nominate. Hypothetically, the same team could bid on and win the first 16 players in the draft and be done (usually happens around pick 140 or so though).

Every year around this time I try and write a formula that can logically assign who is next automatically based on what happens in the auction - I have cells tracking how many players each team has, so I can trigger something when someone reaches 16, but I can't figure out how to program this (seemingly simple) operation.

A1 Team A
A2 Team B
A3 Team C
A4 Team D
A5 Team E
A6 Team F
A7 Team G
A8 Team H
A9 Team I
A10 Team J
A11 - [Want to put a formula here that will check to see if Team A has 16 players already - if he does not, it returns Team A, if it does, it checks Team B, and sticks him in IF he has less than 16, and so on - it could skip all the way down to Team J]

We usually just list the order as it should be, and do it by hand at the end, but it bothers me to know this is something Excel *could* do, and I'm standing in the way... any help would be appreciated!

 

[tyger0951]

Just a bit confused do you have a copy of the sheet you can upload? Where is the amount of players stored in the sheet to reference. ?

If your familar with vBa coding I would suggest something like :

 Sub HighlightMax ()
 
   For I = 1 to 16 'Change To how many teams you have
      If Range("B" & I).Value = 16 Then  ' Checks number of players in team. IF number of players is in col B  
      	Range("A" & I).Interior.Color = vbRed  ' Change The Cell in Column A Red if nummber is 16
      End IF
    Next 
    
End Sub

 

[kvsrinivasamurthy]

Validate the cell A1 with Custom and drag down.

=COUNTIF($A$1:$A1,$A1)<=16