Formula: Next person on a list that does not meet a specific criteria

[Snitches]

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.

 

[x65140]

I would be a bit more helpful if you posted an example - that way some of the gurus could try and adjust your formula instead of starting from scratch and making something that won't fit in your scenario.

 

[Zyg D]

Hi, Snitches.

After reading carefully through your post, I did come up with something. I don't want to disappoint you, but this is VBA solution, not formula. I don't think it would be possible to achieve this goal using formulas. However, my VBA is also not very good looking. I believe someone could do better job.

So, I pasted the given 12 names in the cells A1:A12. In the cells B1:B12 I put the number of selected players. Sheet name is "Sheet2". My code bolds the name of the bettor whose turn it is to start betting for a player. It is done automatically with help of Excel events.

This code should be put in the respective Worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)    
    On Error GoTo errH
    Dim cell As Range
    Dim rCall As Range
    Dim bFlag As Boolean
    Dim i As Integer
    
    Application.EnableEvents = False
    For Each cell In rNames
        If cell.Font.Bold = True Then
            Set rCall = cell
            Exit For
        End If
    Next
    If rCall Is Nothing Then
        Set rCall = rNames.Cells(rNames.Row, rNames.Column)
        rCall.Font.Bold = True
    End If
        
    bFlag = False
    If Application.Sum(rTotalPlayers) > sumPlayers Then
        rCall.Font.Bold = False
        For i = 1 To rNames.Count
            If rCall.Row <> rNames.Row + rNames.Count - 1 Then
                Set rCall = rCall.Offset(1)
            Else
                Set rCall = rNames.Cells(rNames.Row, rNames.Column)
            End If
            If rCall.Offset(, iDistance) < iLimit Then
                Exit For
            End If
        Next
        rCall.Font.Bold = True
                
    ElseIf Application.Sum(rTotalPlayers) < sumPlayers Then
        rCall.Font.Bold = False
        For i = 1 To rNames.Count
            If rCall.Row <> rNames.Row Then
                Set rCall = rCall.Offset(-1)
            Else
                Set rCall = rNames.Cells(rNames.Row + rNames.Count - 1, rNames.Column)
            End If
            If rCall.Offset(, iDistance) < iLimit Then
                Exit For
            End If
        Next
        rCall.Font.Bold = True
    End If
    sumPlayers = Application.Sum(rTotalPlayers)


errH:
    If Err.Number <> 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If
    Application.EnableEvents = True
End Sub

In ThisWorkbook module you should put this code:

Private Sub Workbook_Open()

    ' change the following row according to the cell, where the first name is
    Set rNames = ThisWorkbook.Sheets("Sheet2").Range("A1")
    
    ' distance between Names and Players' count columns
    iDistance = 1
    
    ' Limit
    iLimit = 27
    
    Set rNames = Range(rNames, Range(Left(rNames.Address, 2) & Rows.Count).End(xlUp))
    Set rTotalPlayers = rNames.Offset(, iDistance)
    sumPlayers = Application.Sum(rTotalPlayers)
End Sub

Finally, insert a new module (right mouse click on any of the first two modules > Insert > Module). In this newly created module (Module1) you should paste this code:

Public rNames As Range
Public rTotalPlayers As Range
Public sumPlayers As Integer
Public iDistance As Integer
Public iLimit As Integer

Regards.