NFL Draft Order Results Spreadsheet

[mcianfarani]

Hello,

I am trying to automatically populate the results of my NFL Fantasy Draft into the below table, which is on 1 tab in my workbook.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Draft Order[/TD]
[TD]Player[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the chart below (which is on another sheet in the workbook), I was hoping there was an easy formula to populate the 'order' column once I enter in the Team that drafts the player. For instance, when I type Team C beside David Johnson, it automatically enters "1" under the order column. Then, if Aaron Rodgers is selected next by Team B, it populates a "2", and so on.

Then I was thinking I can, in the other sheet, use a v-lookup to search by "1" and it will populate the player...

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Position[/TD]
[TD]Cost[/TD]
[TD]Team Drafted[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD]Tom Brady[/TD]
[TD]QB[/TD]
[TD]$30[/TD]
[TD]Team A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Aaron Rodgers[/TD]
[TD]QB[/TD]
[TD]$27[/TD]
[TD]Team B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]David Johnson[/TD]
[TD]RB[/TD]
[TD]$29[/TD]
[TD]Team C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Andrew Luck[/TD]
[TD]QB[/TD]
[TD]$25[/TD]
[TD]Team D[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]












I am struggle with the 'order' column formula

Hope that makes sense!

Thank you!

 

[Joe4]

Welcome to the Board!

Use an Index/Match formula, as shown here: Excel formula: Basic INDEX MATCH exact | Exceljet

So, if bottom table where on Sheet1 in range A1:E5, and your top table were on Sheet2 in range A1:B5, then enter this formula in cell B2 of Sheet2 and copy all the way down to B5:

=INDEX(Sheet1!A$2:E$5,MATCH(A2,Sheet1!E$2:E$5,FALSE),1)

 

[mcianfarani]

Hi Joe,

Thanks for the welcome and the reply.
I don't think this works for me. I understand it will pull the correct name of the player drafted using Index/match, but I am as you will notice from the chart, the order is not 'top-down'. I am looking for a formula that will input the order of the players drafted since they are ranked #1-50 by overall ranking. I am looking to show order by draft.

Welcome to the Board!

Use an Index/Match formula, as shown here: Excel formula: Basic INDEX MATCH exact | Exceljet

So, if bottom table where on Sheet1 in range A1:E5, and your top table were on Sheet2 in range A1:B5, then enter this formula in cell B2 of Sheet2 and copy all the way down to B5:

=INDEX(Sheet1!A$2:E$5,MATCH(A2,Sheet1!E$2:E$5,FALSE),1)

 

[mcianfarani]

Maybe I should make this more clear.

[TABLE="class: cms_table_grid, width: 300, align: left"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Position[/TD]
[TD]Cost[/TD]
[TD]Team Drafted[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD]Tom Brady[/TD]
[TD]QB[/TD]
[TD]$30[/TD]
[TD]Team A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Aaron Rodgers[/TD]
[TD]QB[/TD]
[TD]$27[/TD]
[TD]Team B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]David Johnson[/TD]
[TD]RB[/TD]
[TD]$29[/TD]
[TD]Team C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Andrew Luck[/TD]
[TD]QB[/TD]
[TD]$25[/TD]
[TD]Team D[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


















Say "team drafted" column is blank and then I manually enter Team C first, I want the formula to spit out a "1" beside the team. Then, say Team B selects Aaron Rodgers next, I want a "2" to display automatically even if Aaron Rodger is above David Johnson.

Thanks again all!

 

[Joe4]

Whoops, it appears that I had it backwards, updating the wrong table.
It is still an INDEX/MATCH formula.

So the formula would go in cell E2 on Sheet1 (and copy all the way down to E5):

=INDEX(Sheet2!A$2:B$5,MATCH(A2,Sheet2!B$2:B$5,FALSE),1)

 

[mcianfarani]

Hi Joe,

I don't think that will work because there is nothing to match both tables unless I manually enter 1,2,3,4,etc. I was hoping there would be a formula to generate the order of the players drafted based on timing of when I enter data in the column beside it.
Sounds like a macro to me?

Thanks again.

Whoops, it appears that I had it backwards, updating the wrong table.
It is still an INDEX/MATCH formula.

So the formula would go in cell E2 on Sheet1 (and copy all the way down to E5):

=INDEX(Sheet2!A$2:B$5,MATCH(A2,Sheet2!B$2:B$5,FALSE),1)

 

[Joe4]

Wow, I apologize. I am having a bad Monday reading here. To automatically populate column E with that counter when column D is populated will require VBA.
Just right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the following code in the VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim nextNum As Long

'   Update column E with number when column D is updated
    If Target.Count = 1 And Target.Column = 4 And Target.Row > 1 Then
'       Find largest value in column E and add 1 to it
        nextNum = Application.WorksheetFunction.Max(Range("E:E")) + 1
'       Populate column E with value
        Target.Offset(0, 1) = nextNum
    End If
    
End Sub

This should do what you want.

 

[mcianfarani]

Hi Joe,

This worked! Thank you for this.

Would you be able to give me the VBA code if I wanted to populate Column A in the below chart (order) when I input something in column G (Team Drafted)?

[TABLE="width: 649"]
<tbody>[TR]
[TD]Order[/TD]
[TD]Team[/TD]
[TD]Yahoo Rank[/TD]
[TD]Offense[/TD]
[TD]Position[/TD]
[TD]Team[/TD]
[TD]Team Drafted[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]Aaron Rodgers[/TD]
[TD]QB[/TD]
[TD]GB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]Tom Brady[/TD]
[TD]QB[/TD]
[TD]NE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]Andrew Luck[/TD]
[TD]QB[/TD]
[TD]Ind[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]Drew Brees[/TD]
[TD]QB[/TD]
[TD]NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]Russell Wilson[/TD]
[TD]QB[/TD]
[TD]Sea[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Also - how do I undo a macro? For example, if I mistakenly type something G2, but I meant to type something in G3, Column A will not readjust the order (i.e. I cannot undo a macro using 'ctrl-z', right?)

Thank you!

 

[Joe4]

Would you be able to give me the VBA code if I wanted to populate Column A in the below chart (order) when I input something in column G (Team Drafted)?

I tried to document my code so you could follow along and make the necessary changes.
The key to understanding is the VBA Column reference is number, so column A=1, B=2, C=3, D=4, etc.
So it would be something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim nextNum As Long

'   Update column A with number when column G is updated
    If Target.Count = 1 And Target.Column = 7 And Target.Row > 1 Then
'       Find largest value in column A and add 1 to it
        nextNum = Application.WorksheetFunction.Max(Range("A:A")) + 1
'       Populate column A with value (A is 6 columns before column G)
        Target.Offset(0, -6) = nextNum
    End If
    
End Sub

Also - how do I undo a macro? For example, if I mistakenly type something G2, but I meant to type something in G3, Column A will not readjust the order (i.e. I cannot undo a macro using 'ctrl-z', right?)

Undo does not work on macros (as macros are a combination of many steps, not just one). You would have to create some special VBA code for undoing things. Quite frankly, I never bother, as it usually isn't worth the effort (it isn't easy).
See: Excel Tips From John Walkenbach: Undoing A VBA Subroutine