Excel Vlookup & No Huddle Wristband

You can make your own wristband excel sheet using a vlookup, data validation, and an IF statement with a master (coaches) worksheet. Your master worksheet would be where you populate your play sheet with a "drop down list" and a worksheet for each position (X,Y,Z,F,H) that populates based on the coaches call sheet (that you select). Then each play (assignment) would be populated by conditional formatting. IF worksheet is "F" and play is "91", then return "O.S." in the assignment field......




















THIS IS JUST AN EXAMPLE OF HOW-TO (not actual play sheet or route combos) - LIKE THIS

• Make a master list (orange)


• make a coach's play call sheet (blue)


• the position play sheet (red) is just a slave to the data you have in the other two.

1. fill out the master play sheet with all the position assignments
2. You can number your coaches play sheet so you know what you are calling....the 2nd column (the series numbers) is where you would DATA VALIDATE (as a 'list' and select the column of series numbers). Validating it will give you your choices of selection (we run such-and-such plays, you are just going end up with a drop-down list of all those plays)




3. Now, you just want to have the worksheet pull whatever information is under your play (series) number. So if I have the '94' series, I want the appropriate play name to be listed in the column beside it to give me the description













4. The player sheet will just have copy whatever is in the coach's play sheet. Meaning, you just type "=A2", "=A3", etc in the sequential order. So whatever you type in the 2nd column of the coach's sheet will return that value to the 2nd column of the position sheet.Now you just do the vlookup just the same for the player/position assignment (vlookup) EXCEPT for the "Col_index_num" you want the formula to return the number of columns over from the series name. In this case, the Y column happens to be 4 columns from the start of the table (series,play,X,Y,.....)
Be sure to include the '$' character before each Column & Row listing in the vlookup 'table-array' to keep the lookup reference static.Once you have a master table of all the plays you are going to run built, then build those formulas, this thing would be bullet-proof forever...meaning, all you'd have to do is select your "play-call sheet" and the other sheets would populate themselves (you'd never have to enter any data on the position sheets), just print them out for the players.

what is different than most wristband sheets, is that they have a master play bank, then they do a copy & paste job to the other worksheets.

The link I provided does some things differently;

1. Vlookup of information onto the individual assignment bands AND into the playcall sheet


2. cell validation = drop down menu of formations & plays


3. tying the formation & play with a multiple IF statement string-- the tweaks eliminate any possibility for human error by tying everything to the original formation + play

PLAYBOOK tab hosts all the information - this tab feeds all the other worksheets

MASTER tab "loads" the formation & play **columns Q - AD in the MASTER tab are what drives the cell validation (list). These columns are just taking the information from the PLAYBOOK cells.

Now, with the formula (IF) tying the formation & play together

=IF($B12=SPREADRIGHT,(VLOOKUP($C$2,$S$3:$AN$34,4,FALSE)),IF($B12=SPREADLEFT,(VLOOKUP($C12,$S$36:$AN$67,4,FALSE)),IF($B12=TRIPSRIGHT,(VLOOKUP($C12,$S$69:$AN$100,4,FALSE)),IF($B12=TRIPSLEFT,(VLOOKUP($C12,U108:AP139,4,FALSE)),IF($BC12=S12,"",)))))

This is essentially saying

IF .......the formation is "SPREAD RIGHT" go find the play (from the PLAY BANK) that matches the criteria of plays within the SPREAD RIGHT play listings.
IF ......the formation is "TRIPS RIGHT" go find the play (from the PLAY BANK) that matches the criteria of plays within the TRIPS RIGHT play listings.To summarize, go to the PLAYBOOK tab and change the names of the formations (or plays).

if you need the spreadsheet it is here;

My Public Google Docs Account
(Wristband_workbook )

I have received a lot of questions about this spreadsheet, so here is some more information about modifying /customizing it.

You can do this several ways to fix this, but the easiest is to go to the MASTER tab, select the whole sheet, and change the font color to black (or something other than the background color).

You will see the columns from Q to AN are filled with data.

This data is just 'copying' the information that is in the "Playbook" tab.

The Q2-Q5 cells are what feeds the 'formation' validation (drop down list).

These formations are available in drop-down in the B column of the MASTER tab.

If you change the formation, then you will also have to correct the conditional IF statement found in the player assignment cells (D2-N100)

example

=IF(B7=SPREADRIGHT,(VLOOKUP(C7,$S$3:$AN$34,2,FALSE)),IF(B7=SPREADLEFT,(VLOOKUP(C7,$S$36:$AN$67,2,FALSE)),IF(B7=TRIPSRIGHT,(VLOOKUP(C7,$S$69:$AN$100,2,FALSE)),IF(B7=TRIPSLEFT,(VLOOKUP(C7,S103:AN134,2,FALSE)),IF(B7=Q7,6,)))))

Change the instance of the name of the formation (and appropriate cells you reference when looking up)

PS - if you downloaded an earlier copy that has protected cells, the password should be "youth" (from what I remember).