# Need Complex Formula Help for Volleyball Rotation

This is a discussion on Need Complex Formula Help for Volleyball Rotation within the Excel Questions forums, part of the Question Forums category; I am creating a volleyball rotation chart. I have the lineup in B7:B16 for this example. If you study Rotation ...

1. ## Need Complex Formula Help for Volleyball Rotation

I am creating a volleyball rotation chart. I have the lineup in B7:B16 for this example. If you study Rotation 1-3, you will see that from Rotation 1 to Rotation 2, Name 1 leaves the rotation, Name 7 comes in at location 6, location 6 goes to 5, 5 to 4, 4 to 3, 3 to 2 and 2 to 1. Note that Name 1 does not leave the game but goes to the bottom of the rotation.

I am only showing three Rotation panels but there are actually 15 total which allows me to have 15 players in the lineup and show all 15 rotations.

So here is what I need... I want a formula in F13, F9, E9, D9, D13 and E13 that will rotate the lineup through as noted. But here is the kicker, if one team has 9 players another has 15 and another has 12, the formula should adjust the number of players and leave the unused rotations blank - i.e., if there are 10 players then only Rotation 1-10 will be used and 11-15 will be blank. If there are 9 players, then Rotation 1-9 will be used and 10-15 will be blank.

For now I have brute forced it but this will only work for a lineup of 10 players. I am thinking this will require the use of index and offsets, I just can't figure it out after hours of attempts.

Thanks for the help!!!!!

******** ******************** ************************************************************************>
 Microsoft Excel - 2009 Fall Volleyball.xls ___Running: 11.0 : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D9E9F9I9J9K9N9O9P9D13E13F13I13J13K13N13O13P13 =

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
6
Input Lineup Here  Rotation 1    Rotation 2    Rotation 3
7
1Name 1 432  432  432
8
2Name 2
9
3Name 3 Name 4Name 3Name 2  Name 5Name 4Name 3  Name 6Name 5Name 4
10
4Name 4
11
5Name 5 561  561  561
12
6Name 6
13
7Name 7 Name 5Name 6Name 1  Name 6Name 7Name 2  Name 7Name 8Name 3
14
8Name 8
15
9Name 9
16
10Name 10
 Code

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

2. ## Re: Need Complex Formula Help for Volleyball Rotation

Hi

I have re-arranged the rotations for easy formula copying. The formulae in rows 9 to 12 are copied from row 8.

 A B C D E F G H 6 Rotation 1 Rotation 2 7 1 Name 1 4 Name 4 3 Name 3 8 2 Name 2 3 Name 3 2 Name 2 9 3 Name 3 2 Name 2 1 Name 1 10 4 Name 4 1 Name 1 10 Name 10 11 5 Name 5 10 Name 10 9 Name 9 12 6 Name 6 9 Name 9 8 Name 8 13 7 Name 7 14 8 Name 8 15 9 Name 9 16 10 Name 10

 Cell Formula E7 =INDEX(\$B\$7:\$B\$21,D7) G7 =IF(D7-1=0,MAX(\$A\$7:\$A\$21),D7-1) H7 =INDEX(\$B\$7:\$B\$21,G7) D8 =IF(D7-1=0,MAX(\$A\$7:\$A\$21),D7-1) E8 =INDEX(\$B\$7:\$B\$21,D8) G8 =IF(D8-1=0,MAX(\$A\$7:\$A\$21),D8-1) H8 =INDEX(\$B\$7:\$B\$21,G8)

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

If you have fewer players the speadsheet looks like this:

 A B C D E F G H 6 Rotation 1 Rotation 2 7 1 Name 1 4 Name 4 3 Name 3 8 2 Name 2 3 Name 3 2 Name 2 9 3 Name 3 2 Name 2 1 Name 1 10 4 Name 4 1 Name 1 8 Name 8 11 5 Name 5 8 Name 8 7 Name 7 12 6 Name 6 7 Name 7 6 Name 6 13 7 Name 7 14 8 Name 8 15 16

3. ## Re: Need Complex Formula Help for Volleyball Rotation

Hi - is there somewhere that I can download the xls file for this volleyball rotation?

This would definitely be useful!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•