The Custom Draft Tool

Customizable Draft Sheet that lets you draft anything you want.

My fantasy league recently went from 14 to 12 managers and we hosted a League Reduction Draft to distribute the players from the 2 teams that were getting removed. I put together this draft sheet quickly to get the job done. It's a simple format but worked well through 4 rounds of our reduction draft. I took that framework and used it as an excuse to solve some problems I have had in the past and I am now giving it away.

This one is customizable, from 2 to 16 teams, 1 to 30 rounds, and optional Snake or Traditional draft formats. You can add any data to the list to be drafted, and you can see all the results on a dynamic results page that will work for any number of picks.

I need to start pay walling this stuff but for now, this is my Christmas gift to you guys. So now you can draft anything: Christmas movies, the best players whose names start with J, or my favorite offseason draft theme…Breakfast Foods.

Custom Draft Tool:

https://docs.google.com/spreadsheets/d/1HNe9BUYfaGx9xE_U4SL-4AFlqHflWQlDGwHf96iZrGw/edit?usp=sharing

(View only link, make a copy to have your own version)

Look now you feel awkward because we said no gifts this year, but if you subscribe to my substack, we will call it even.

This is where most people will stop reading. Thanks again for checking out my projects, I have maybe 1 more post for January, and then I will post regularly through the Fantasy Baseball Pre-season.

Keep reading if you would to know how to make the dynamic results page…

Dynamic Lists with Arrays // Draft Results Page

I wanted a dynamic list that could show between 2 and 16 unique lists based on user inputs. The outcome of the Custom Draft Tool is 3 arrays that create the 3 columns of lists that expand as managers draft players. In this example, I have 6 columns of 6 arrays. While there are a few ways to create a draft results page, all of them fall short if you want the round and team amount to be customizable.

Example List

I could use what I call a blank check, where an IF statement checks for null values

=if(A1=" ", " " , "List 1")

that would hide the lists I don't need, but that is not dynamic. What happens if List 1 has 30 values, it would overlap List 7 below it. The maximum is 30 items so I could leave space for that, but when there are 20 items or just 5 items, my lists will be far apart on the screen with too much white space. I want it to look nice. So that is where {Arrays} come to the rescue.

Setting up an array for each column of Lists:

{ “List 1”, "" ; Query Function Looking for List 1 Values ;
“List 7”, “ “; Query Function Looking for List 7 Values;
“List 13” , “ “; Query Function Looking for List 13 Values}

Comma → separates columns
Semi-Colon → separates rows
*Queries have 2 columns
One important note about arrays is that all the rows have to have the same number of columns. I fill up the empty space by adding two quotes “ “ to denote an empty string (empty space).

Result using random data:

={"List 1", " ";
query($O$3:$Q$143, "Select Col2, Col3 where Col1 = 'List 1'",0);
"List 7", " ";
query($O$3:$Q$143, "Select Col2, Col3 where Col1 = 'List 7'",0);
"List 13" , " ";
query($O$3:$Q$143, "Select Col2, Col3 where Col1 = 'List 13'",0)
}

Showing Random Data with the full Dynamic List:

This was the first time I had a good reason to use (and learn how to use) arrays in a project. I separated each array function by column (List 1, 7, 13 together) but you could make all 16 lists with the same array, just spacing everything out in the array function following the rules above.

Hope this helps with a future project and thanks for reading!