SOLVED

Creating sports wrist play call sheet arm band

Copper Contributor

Hi,

 

I coach softball and I am trying to learn how to create a play call sheet for my player's wristbands that they wear during the games.  I will signal the play to the catcher and she will relay it back to the pitcher.  I am trying to learn if there is a way I can create these play sheets for my players that are a random alpha/numeric combination.  I will have letters for the type of pitch, and then combine that with one of the four pitching zones for the numeric portion (1-4).  For example, a dropball down and away (D3) in my screenshot below would be found in 151 (row first 1, then column 15).  The goal is that I combine the type of pitch (F=fastball, c=change up, d=dropball, and r=riseball, v=curve ball) with the zones (1-4) without having to enter the patterns manually in each and every cell.  I would also like to easily change the combinations too.  Is there a formula or way that I can do this for 5 more sections as you see below?  Here is a screenshot of one section of what I am trying to accomplish.  Thank you!

BigWheels8_0-1625757681597.png

 

 

 

 

15 Replies

@BigWheels8 , There are formulas like RANDBETWEEN and CONCATENATE which can help. The RANDBETWEEN formula uses numbers, but using HLOOKUP formula, you can return an alphabet. See attached file for sample.

 

By pressing F9, the values will change to create a new combination.

 

 

=CONCATENATE(HLOOKUP(RANDBETWEEN(1,5),$C$4:$G$5,2,FALSE),RANDBETWEEN(1,4))

 

 

amit_bhola_0-1625767299366.png

 

The answer is yes but some details are still confusing to me.
First off, lets get some conventions straight. you say row first 15, then column 1 but 15 is the title of the column and 1 is the title of the corresponding row so if you want 151 that would be column-row.
Next a question of numbers: you give 4 numbers and 5 letters which makes 20 unique combinations but the grid is 6x6 which is 36 possibilities. Do you WANT to have repeats in the grid (as shown in your image you example D3 is 151 AND 102 AND 105 AND 142). Maybe I should ask instead is if the grid will always be that 6x6? After using up all the combinations should the rest be filled in? Is it ok to fill in the rest in the same 'random' order (e.g. 100 would be same as 123 and then 110 would be same as 133 ...)? Or should the grid resize (e.g. columns would be 10 - 14 because there are 5 possible letters and rows would be 0 - 3 because 4 possible numbers but the actual location of each combination would still be randomized)?
And the MOST important question I have is if you have Excel 365 (i.e. the latest version with LET() function and dynamic arrays)? Basically type =LET and see if excel recognizes that as a function.
best response confirmed by allyreckerman (Microsoft)
Solution

@BigWheels8   maybe @amit_bhola  solution is good enough, that would be great.  But for the fun of it I worked out a sheet that might be useful.  you can easily print it to both show the grid and the definitions of the numbers/letters and easily change and it guarantees that every possible combination is used at least once (assuming the grid is large enough).  File attached.

mtarler_0-1625770790013.png

 

amit_bhola - thank you for the formula ideas and especially the sample spreadsheet. I am going to play with it and familiarize myself with these formulas.
mtarler-sorry, I confused the rows and columns. I corrected that in my post. Anyway, to answer your questions:

1. Yes, I do want to have repeat signs throughout the grid after the 20 unique combinations are used.

2. I plan on using the 6x6 grid and repeating it. The rows in the first grid I would use before the sample would be 00, 01, 02, 03, 04, 05. Followed by my example of 10, 11, 12, 13, 14, 15. Next, I would skip count and the next section will be 20, 21, 22, 23, 24, 25, etc. The first column will always be 0-5.

3. I have MS Office 2016
mtarler-thank you as well. I appreciated the sample spreadsheet you created. I am reading through the formula and trying to follow it, but need more time to see if I can understand it. This is way over my Excel paygrade! Thank you again!
Is there a way to take this, then have it return the numbers around the grid? I want a column in a separate sheer named 'F1' to lookup all the F1 values, then underneath, have a formula that gives me all the different combos....and the cell color.
Ex: I want to make a playsheet that gives me all the pitches, and the code that corresponds...returning the number in the header first, then the number on the side. I will make a column for each 'pitch' so I can easily make calls.
F1
*(each set of calls is highlighted in the cell's color)
10, 0 (highlighted in the cell color)
11, 5
13, 0
14, 1
14, 4

Appreciate your help!

@ingizmo In amit_bhola sheet I believe all the cells are randomly assigned so a reverse look up table as you request although possible (nearly everything is) but would take me a bit of thinking (I'll try to avoid, lol).
In the solution I posted above (and corrected in the attached) the randomized order is just repeated so cell I2 (row "0" and column "10") is repeated in cell K5 (row "3" and column "12") and then the sequence repeats until it fill the whole table. With added work it could add more randomization but this technique has a few advantages:
a) it guarantees all options used at least once,
b) it prevents the same call from having the same row/col (assuming the count of calls > columns and not an even multiple)
In the attached I:
- created a new column so after you have a everything set up you can copy and paste the VALUES ONLY to this new column and 'LOCK' the grid so it won't change all the time
- created a lookup column under the grid that creates that reverse lookup you requested. The column right now just uses col F but any order can be inserted there. The formula uses TEXTJOIN to show the multiple options with a separator and uses " ; " right now but that can be easily editted or the formula could be modified to separate the results into separate cells.
- corrected an ERROR in the original post above where I didn't offset the MOD result and hence repeated the first call and missed the last call in the grid

BTW you can ignore the 1st paragraph above as the solution I ended up including in the attached sheet should work equally well on amit_bhola's solution also.

@ingizmo , looking up colors is difficult (It is possible by using macros, but a bit complicated so i am avoiding it).

For looking up values in a column, Mr. @mtarler has already given a solution.

 

There is a middle solution which i propose which looks up colors as well - if it suits your purpose.

(Well in reality, it actually masks the colors of "not found" cells to white, but the effect is same). However, the compromise is that this implementation doesn't arrange the results for one pitch in one column, but rather it arranges it in a rectangular grid same as original.

 

See attached File and press [F9] to generate new combinations.

 

amit_bhola_0-1653074077879.png

 

 

 

Oh yeah, i forgot to address the whole color thing. As mentioned, looking up colors can only be done using a macro which I too will not address here.
The next question is what/how/why are you making colors? So are all F1 have the same color? or is it like amit did above with a random array of colors?
So depending on the answer, another option is to assign colors using conditional formatting and depending on how the formula is designed use that same formula to assign the same coloring for the 'matching' cell (easy if all 'F1' are green, ... , and harder if based on row/column since additional column/row would be needed to identify what row/col the look-up value came from.)

@amit_bholaThank you! I'll dig more into the macro side. I've added a lookup concate add-in once in VBA and used to play, but it has been a few years. I'll review what you sent and see if I can't make it work! Thanks again!

I use the colors as another way to call a pitch. I use the color and the left column. So in your example above in the latest response, I could say '10, 0' or 'Pink 0' to call the upper left pitch 'C2'. Each color is unique to a pitch. I don't use the same pitch in the same cell two games in a row so I am always changing them, I appreciate your time and really appreciate your help!

@ingizmo Updated the file to 

a) split lookup info into separate cells

b) use conditional formatting to color cells

In every tournament and game in the Champions League, referees are crucial. Similar to this,

How to Become a Tennis Sports Referee is familiar with all of the game's guidelines to help them make the best choice. The task of a referee is relatively difficult since they must determine the proper scoring. You're at the proper place if you're considering becoming a pickleball referee.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@BigWheels8   maybe @amit_bhola  solution is good enough, that would be great.  But for the fun of it I worked out a sheet that might be useful.  you can easily print it to both show the grid and the definitions of the numbers/letters and easily change and it guarantees that every possible combination is used at least once (assuming the grid is large enough).  File attached.

mtarler_0-1625770790013.png

 

View solution in original post