Instructions
The Files You Will Need
I know there are a lot of steps below, but don't worry - each step is pretty simple.
You will need to copy two of my Google Sheets files for use in your league. For each one, click on the link below to open it in Google Sheets. Then in Google Sheets, click the Make A Copy option in the File Menu. You will probably want to give them meaningful names like "12 Angry Men - Master Draft Board" and "12 Angry Men - Custom Rankings Team 1"
Master Draft Board
This is the master draft board sheet. It contains the main grid that will be displayed on the big screen on draft day. It also contains the names, positions and other information for each draftable player, plus their default draft rankings.
Custom Rankings - Team 1
This is the sheet to be used by one league member. Once this file is configured to work with your master draft board sheet, you will make copies for every other league member.
Configure Your Master Draft Board
Open your newly copied master draft board and make the following changes:
Go to the "Team Names, League Size and Draft Order" worksheet
- Enter value for League Size (up to 16)
- Enter value for Number of Rounds (up to 20)
- Enter team names
- Enter names of keepers, if any
Go to the "Overall Rankings" worksheet
- Enter or paste in player data including Name, Position, Team, Bye Week, Initial Ranking and Average Draft Position
Go to the "Actual Draft Results" worksheet
- Hide columns on the right for unused teams. Hide rows for unused draft rounds.
- Each team has 2 columns. The first column will automatically show the player position and bye week, and will be color coded
- Note: do NOT edit the first column for each player
- If you have Keepers, enter the names in the second column for each team in whatever round they should be kept. These should automatically show as highlighted in yellow
Configure The Custom Rankings Sheets
Open your newly copied custom rankings for team 1 and make the following changes
- Open your Master Draft Board sheet in your browser and copy the URL.
- Go to the "Instructions" worksheet and paste the URL in cell C4 (it's highlighted). The URL will look something like "https://docs.google.com/spreadsheets/d/xxx-yyyyyyyyyyyyyyy-zzz/edit#gid=1147144904"
- Cell C5 will automatically extract the master board sheet ID. This will automatically link the custom rankings sheet to the master draft board sheet
- Go to Board Copy worksheet
- Unhide columns A through C if they are hidden. If hidden, you will see a small arrow in the D column label.
- Click on cell A1. This contains the import command to import data from the master draft board sheet
- You should see a message "You need to connect these sheets". If you see "#REF!" in this cell, you may not have pasted the correct URL in the Instructions sheet.
- Click the Allow Access button if prompted
- You should see the contents of the Board Copy worksheet fill with the contents of the Actual Draft worksheet in the Master draft board sheet
- Go to the Players worksheet and click the Allow Access button if needed
- NEW for 2017: Go to the Tier Availabity worksheet. If you see any "N/A" values in row 5, it means Google did not recalculate the queries. To fix this, edit all the formulas in row 5 that show N/A by adding a space to the end of the formula and pressing the enter button.
- Cell A1 should now say "(Import)"
- As a test, go to the Master Draft Board and enter a name like "Dez Bryant" in cell F1
- You should see that change also show in the Board Copy of this Google sheet. This shows the two sheets are now linked
- Hide columns A through C in the Board Copy worksheet
- Hide / unhide columns and rows to match the number of teams and number of rounds for your league
- Note: NEVER make changes to the Board Copy or Players worksheets. It will mess up everything
Share the Sheets with League Members
Now we need to make sure all league members have view access to the master draft board and edit access to their own custom rankings sheets
- Once you make sure the Custom Rankings sheet works for team 1, make a copy of the file for every other team in the league and rename as appropriate
- Share the Master Draft Board sheet to all team members but ONLY with read only access
- For each league member, share their specific Custom Rankings sheet with owner access. League members should not have access to each other's rankings.
Instructions for League Members
Now that you've shared the custom rankings sheets, give your league members instructions on how to use it. Let them know:
- Members will never need to directly open the master draft board sheet, although their custom rankings sheet will use it behind the scenes.
- The only worksheet members should work in is the "Custom Rankings" worksheet
- The only columns that members should update are B, C, D and J. Please don't touch the other columns.
- To use your own rankings, simply change the ranking number in column B of the Custom Rankings tab. Nobody else will see your rankings
- You can re-sort or filter by any column you want
- If you want to paste in rankings from another web site, clear the data values in columns B and C, then copy and paste into just those 2 columns.
- NEW for 2017: if you enter values in the My Tier column, you can use the new Tier Availabilty to display your custom tier rankings. And durring the draft, these tiers will automatically update.
- If you want to reset your rankings to whatever is in the master draft board, click the Initialize button and follow the instructions
- NOTE: The player names in column C must exactly match the player names in the master draft board sheet. If they don't, you will see "#N/A" in the columns to the right of the name. Make sure you don't have any extra spaces at the beginning or ending of the name. Also different web sites can have different variations of a player name. For example, "Odell Beckham Jr." vs. "Odell Beckham". You can check the Players tab to search for the correct spelling.
- During the live draft, you will see the player status change from Available to Selected
Summary
Hopefully you were able to follow the instructions to get your league ready for Draft Day. Hope yours is the best one yet!