Need Complex Formula Help for Volleyball Rotation

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
150
Office Version
  1. 365
Platform
  1. Windows
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!!!!!

2009 Fall Volleyball.xls
ABCDEFGHIJKLMNOP
6InputLineupHereRotation1Rotation2Rotation3
71Name1432432432
82Name2
93Name3Name4Name3Name2Name5Name4Name3Name6Name5Name4
104Name4
115Name5561561561
126Name6
137Name7Name5Name6Name1Name6Name7Name2Name7Name8Name3
148Name8
159Name9
1610Name10
Code
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi

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




<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 94px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 96px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD><TD>Rotation 1</TD><TD> </TD><TD> </TD><TD>Rotation 2</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Name 1</TD><TD> </TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Name 4</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Name 3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Name 2</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Name 3</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Name 2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Name 3</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Name 2</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Name 1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Name 4</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Name 1</TD><TD> </TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Name 10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Name 5</TD><TD> </TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Name 10</TD><TD> </TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">Name 9</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">Name 6</TD><TD> </TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">Name 9</TD><TD> </TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Name 8</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">Name 7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Name 8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">Name 9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Name 10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E7</TD><TD>=INDEX($B$7:$B$21,D7)</TD></TR><TR><TD>G7</TD><TD>=IF(D7-1=0,MAX($A$7:$A$21),D7-1)</TD></TR><TR><TD>H7</TD><TD>=INDEX($B$7:$B$21,G7)</TD></TR><TR><TD>D8</TD><TD>=IF(D7-1=0,MAX($A$7:$A$21),D7-1)</TD></TR><TR><TD>E8</TD><TD>=INDEX($B$7:$B$21,D8)</TD></TR><TR><TD>G8</TD><TD>=IF(D8-1=0,MAX($A$7:$A$21),D8-1)</TD></TR><TR><TD>H8</TD><TD>=INDEX($B$7:$B$21,G8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


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:


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 95px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 83px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD><TD>Rotation 1</TD><TD> </TD><TD> </TD><TD>Rotation 2</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Name 1</TD><TD> </TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Name 4</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Name 3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Name 2</TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Name 3</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Name 2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Name 3</TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Name 2</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Name 1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Name 4</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Name 1</TD><TD> </TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Name 8</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Name 5</TD><TD> </TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Name 8</TD><TD> </TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">Name 7</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">Name 6</TD><TD> </TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">Name 7</TD><TD> </TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">Name 6</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">Name 7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Name 8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
 
Upvote 0
Hi - is there somewhere that I can download the xls file for this volleyball rotation?

This would definitely be useful!
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,693
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top