NCAA Volleyball Charting Sheet

[coachkc]

I'm not entirely sure if this would be the right area of the forum to be posting this. In fact, the only thing I'm actually certain of is that I'm essentially incompetent when it comes to Excel. The project I'm working on for our NCAA volleyball program is far outside my scope of ability.

A quick overview: In our sport, charting real time in matches (with pen and paper, typically) is paramount for identifying trends and developing dynamic strategy. So it's really a never ending quest to develop "the perfect match chart." For this chart, I am searching to identify trends of our opponents based on our decision making (as spikers and setters). Apologize in advance for the guaranteed confusion.

Attached is what I have completed thus far. The core of what I am after with this sheet is to analyze various rotational matchups. In our sport, there are six players on the court at a time and as the game progresses, they rotate clockwise to the next spot on the court while simultaneously maintaining the rotational order (that is to say: if I start in the Middle Front and Suzy starts in the Right Front, I will rotate to the Right Front position while Suzy rotates to the Right Back position. I will always follow Suzy in the rotation). Because of this rule setup, rotational matchups become very important (especially at higher levels). Matches can be won or lost depending on how our starting rotation relates to the opponents starting rotation. We have 6 possible starting rotations; so does the opponent. Because both teams rotate in order, we know that if we start in rotation 1 and the opponent starts in rotation 1, we will be in rotation 5 when our opponent is in rotation 5 (and so on). This means there are 6 possible rotational matchups in relationship to the opponent (home ro. 1 vs opponent ro. 1, hr1 vs or2, hr1 vs or3, hr1 vs or4, hr1 vs or5, hr1 vs or6). All 36 potential matchups fall within one of those rotational matchups.

In the attached sheet, I have completed the first rotational matchup (hr1 vs or1). I found it to be super time consuming (I've been using the =LOOKUP formula exclusivey) to punch in all those formulas and when I went to copy them to the next "court," the formulas didn't translate and I was forced to rewrite them each individually. I need the next 5 rotational matchups on different sheets and was wondering what I could do to streamline that process.

A basic description for what I am trying to accomplish would be as follows: the two rectangles in the top left corner denote our starting rotation and the opponent starting rotation. Each square in those rectangles is an entry field that controls what is spit out on the 6 courts below. For those 6 courts, each can be roughly cut in half (the bottom half being our home rotation and the top half being the opponent rotation). For all 6 sheets (the current one and the subsequent 5), the bottom half of the courts should never change (nor should the "home" rotational rectangle at the uppermost left corner of the screen). The only way any of that info would change on any of the sheets is if we decided on making a substitution before the match. However, to generate the various rotational matchups, we need the UPPER half of those courts to correspond with a different lower half. Simultaneously, we would need the "opponent" rotational rectangle (next to the "home" rotational rectangle) to rotate once clockwise. This means that on the first sheet (provided) the "S" is in the top left rectangle. On the second sheet, the "S" should be in the top middle rectangle, with every other player rotating once as well. In relationship to the court, I need the top half (opponent half) of the bottom right court to shift in its entirety to then correspond with the fixed bottom half (home half) of the top right court. The third sheet (and subsequent sheets) would all follow suit, rotating once from its location on the prior sheet. Two small side notes: the stand alone boxes next to each rotational rectangle signify a player who plays independent of the rotation, so I need to leave her in the box when rotating. Secondly, the series of codes in the two rectangles on the top right of the sheet are entry spaces for scouting report information that correspond to the rotations. If their values could be transferred as well, I would be extra pumped.

I'm sure this is an absurd request for help and I appreciate in advance any insight anyone can provide.

Thanks!

KC