Streamlining the Removal of Drafted Players From Your Rankings

Your fantasy league may have already drafted. It’s neither good nor bad (though what happens when that young, high-round pitcher blows out his elbow on Thursday?), just a scheduling decision. But if you’ve yet to draft, or plan on joining a late-drafting league just for kicks, have I got a *ahem* life-hack for your draft-day rankings spreadsheet.

It’s always useful to remove players from your rankings as they’re drafted. You don’t get tripped up waiting to pick players that you missed go off the board, and you get to see the best options remaining. Of course, you could “Command-F” and delete the players as they go, but if you’re like me, a person who puts rankings, cheat sheets, depth charts, and ADP data all in the same file, searching for a player can be more troublesome than helpful. So we’re looking to develop a method of wiping away those drafted players without using “Command-F” and maybe with a little marginal utility added, namely creating a table of rosters as you draft.

First, create a table titled “Draft Results.” We want this table to include three columns: Player, Manager, and Round. In the first cell in the column “Round,” code in: ROUNDUP((ROW(A2)−1)÷8,0). Copy the code into the rest of the Round cells. Once you know your draft order, enter the managers’ names into the Manager column corresponding with the round and pick. As your draft proceeds, you’ll be manually entering each player drafted into the sequentially proceeding Player column. This requires the same amount of effort as a “Command-F” search but with more efficiency and utility.

In your main rankings table (titled “Rankings”), you likely already have many columns for overall ranking, position ranking, auction value, ADP, and so on. You’ll need a bit more clutter for this, adding in columns Manager, Round, Pick, and Player Name II after the first column, which contain the player’s name right now. The reason for two cells containing the same player’s name is that the first cell, now containing plain text, will need to contain code that cannot reference the cell it is residing in. Simply cut and paste the player names from your first column into your fifth column, and shrink the cell widths if you don’t want to look at redundant or irrelevant information.

In the first cell under Manager, enter: IFERROR(VLOOKUP(E2,’Draft Results’::Player:Manager,2,FALSE),” “). The E2 references the new location of the player names, and the ” “ will keep you from looking at error messages across your table. This code will enter in the drafting manager in your Rankings table as you enter your draft picks in your Draft Results table. Copy and paste this in each Manager cell in your Rankings table.

In the first cell under Round, enter: IFERROR(VLOOKUP(E2,’Draft Results’::Player:Round,3,FALSE),” “). This accomplishes a similar effect as the code above. As usual, copy this code into the Round cells below. In the first cell under Pick, enter: B2&”-“&C2. This will be used later on when creating your Rosters table.

In the first column, where your player names used to be, enter into the first cell: IF(B2=” “,E2,” “). If a player has not yet been drafted, there should only be a single space as text under Manager. So if the player hasn’t been drafted and no manager has been entered, the cell will return the copied text of the kinda-invisible Player Names II cell, containing the text you moved at the start. Once the player has been drafted, the manager’s name appears and the player’s name disappears from your Rankings table.

If this party trick isn’t quite enough to convince you to add this code into your table, you can use this to create a table of Rosters, which you’ll be able to see during the draft and without clicking through multiple tabs in your draft interface, which is dangerous during a live draft. You’ll need a table with as many columns as there are managers in your league and as many rows as there are rounds in your draft. Label the headers of each column with the same names you used for your opponents in the Draft Results table (it’s important that they match, otherwise this table will remain empty). Label the headers of the rows with the round number (Row(B2)-1 if you don’t like typing). In the cell B2, enter: IFERROR(INDEX(‘Rankings’::$A:$Player Name II,MATCH(B$1&”-“&$A2,’Rankings’::$D,0),5),” “) and copy the this into the rest of the cells in the table. As you enter the drafted players into your Draft Results table, the same names are entered into your Rosters table in the cell corresponding to the drafting manager and the round of the pick.

In a live draft, every second counts. If you can streamline your drafting process even a little, it’s worth the prep beforehand to do so. I hope this helps you on your draft day, unless I’m competing against you, in which case I hope you find yourself in a blackout five minutes before the draft.

Print This Post

newest oldest most voted

I personally prefer putting the name of the player in red when they get picked. Which is just a simple conditional formatting and COUNTIF function. Otherwise, this is a very useful tool in tracking live draft information. Well done.