Adding Increasing Numbers in Columns after Data is Entered

[k2timmy]

Hello,

I'm doing a fantasy football draft soon and I'm trying to find a way to automatically have the pick number appear in a cell next to each players name (Pick # column) after I enter their name. Each time I enter a new name the pick number should go up by one. I will be using five different columns for each of the different teams in the league and players will be entered randomly in one of the five columns after they are chosen.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Pick #[/TD]
[TD]Team One[/TD]
[TD]Pick #[/TD]
[TD]Team Two[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]David Johnson[/TD]
[TD]1[/TD]
[TD]Todd Gurley[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Le'Veon Bell[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I will have a cell that will track the current pick #, but I can't just use a formula to return what's in that cell since it's always changing. Unless I can have it return what's in that cell at the time the data (Players name) was entered? Or is there another way to do this?

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Picks So Far[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

If anyone can help me crack this it would be much appreciated!

 

[Joe4]

I think what you want will require VBA.

Let's say that your headers are in row 1, and starts in column A.
So column A is the first team's pick number, column B is the first team's pick, and then that repeats that pattern for the next, etc out to column J, then the following code will run automatically.
Just right-click on the sheet tab's name at the bottom of the screen, select View Code, and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim teamRng As Range
    Dim pickRng As Range
    
'   Set ranges
    Set teamRng = Range("B:B, D:D, F:F, H:H, J:J")
    Set pickRng = Range("A:A, C:C, E:E, G:G, I:I")
    
'   See if exactly one cell in teamRng is updated
    If Target.Count = 1 And Not Intersect(Target, teamRng) Is Nothing Then
'       Check to see pick cell is blank
        If Target.Offset(0, -1) = "" Then
'           Increment pick counter by one
            Target.Offset(0, -1) = Application.WorksheetFunction.Max(pickRng) + 1
        End If
    End If
        
End Sub

Start entering values in the Team Pick columns, and watch the numbers automatically populate.

 

[k2timmy]

This is PERFECT! Thank you so much!!!

I think what you want will require VBA.

Let's say that your headers are in row 1, and starts in column A.
So column A is the first team's pick number, column B is the first team's pick, and then that repeats that pattern for the next, etc out to column J, then the following code will run automatically.
Just right-click on the sheet tab's name at the bottom of the screen, select View Code, and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim teamRng As Range
    Dim pickRng As Range
    
'   Set ranges
    Set teamRng = Range("B:B, D:D, F:F, H:H, J:J")
    Set pickRng = Range("A:A, C:C, E:E, G:G, I:I")
    
'   See if exactly one cell in teamRng is updated
    If Target.Count = 1 And Not Intersect(Target, teamRng) Is Nothing Then
'       Check to see pick cell is blank
        If Target.Offset(0, -1) = "" Then
'           Increment pick counter by one
            Target.Offset(0, -1) = Application.WorksheetFunction.Max(pickRng) + 1
        End If
    End If
        
End Sub

Start entering values in the Team Pick columns, and watch the numbers automatically populate.

 

[Joe4]

You are welcome -- and good luck in your fantasy league!