Create a rotating list of responsibilities

I'm in Toastmasters and a club meeting consists of members fulfilling different roles. Each meeting has different people serving in different positions. We currently rotate those positions and list them using an Excel spreadsheet that lists out the next 4 weeks worth of meetings.

I am asking you all if you can think a way to automatically manage this list. At this point, we have a column for members and then a column for each week in the schedule. We then type in the person's role for each week. This process is very manual.

Basically, we have a dynamic set number of members and a static set of roles (9 in total). There doesn't have to be a rhyme or reason for the rotation as long as it doesn't burden one person with a role.

Sample 2-week agenda

``````              11-Jan         18-Jan
Person 1   Toastmaster    Speaker #1
Person 2   Evaluator # 2  Evaluator # 1
Person 3   Grammarian     Table Topics
Person 4   Speaker #1     Timer
Person 5   Evaluator # 1
Person 6   Table Topics   Ah Counter
Person 7   Timer          Evaluator # 2
Person 8   Speaker #2
Person 9   ah Counter     Grammarian
Person 19                 Speaker #2
``````
• I know an old question, but I'd have put the roles in a seperate list and then using a random function grabbed one using VBa. A quick check to make sure it wasn't a match to the previous and job done!
– Dave
Jun 14, 2017 at 18:04

Column A is the numbers, that represents the people
Column B is the letters, that represents the roles
Column C is the first series of formulas. You drag C1:C8 (or CX however many you have) across the columns to the right.

``````    1   a   =B8 =C8 =D8 =E8 =F8 =G8 =H8 =I8
2   b   =B1 =C1 =D1 =E1 =F1 =G1 =H1 =I1
3   c   =B2 =C2 =D2 =E2 =F2 =G2 =H2 =I2
4   d   =B3 =C3 =D3 =E3 =F3 =G3 =H3 =I3
5   e   =B4 =C4 =D4 =E4 =F4 =G4 =H4 =I4
6   f   =B5 =C5 =D5 =E5 =F5 =G5 =H5 =I5
7   g   =B6 =C6 =D6 =E6 =F6 =G6 =H6 =I6
8   h   =B7 =C7 =D7 =E7 =F7 =G7 =H7 =I7
``````

specifically, for you, it would look like this

``````A           B        C       D       E       F       G       H
Person 1    Role 1  =B19    =C19    =D19    =E19    =F19    =G19
Person 2    Role 2  =B1     =C1     =D1     =E1     =F1     =G1
Person 3    Role 3  =B2     =C2     =D2     =E2     =F2     =G2
Person 4    Role 4  =B3     =C3     =D3     =E3     =F3     =G3
Person 5    Role 5  =B4     =C4     =D4     =E4     =F4     =G4
Person 6    Role 6  =B5     =C5     =D5     =E5     =F5     =G5
Person 7    Role 7  =B6     =C6     =D6     =E6     =F6     =G6
Person 8    Role 8  =B7     =C7     =D7     =E7     =F7     =G7
Person 9    Role 9  =B8     =C8     =D8     =E8     =F8     =G8
Person 10   Role 10 =B9     =C9     =D9     =E9     =F9     =G9
Person 11   Role 11 =B10    =C10    =D10    =E10    =F10    =G10
Person 12   Role 12 =B11    =C11    =D11    =E11    =F11    =G11
Person 13   Role 13 =B12    =C12    =D12    =E12    =F12    =G12
Person 14   Role 14 =B13    =C13    =D13    =E13    =F13    =G13
Person 15   Role 15 =B14    =C14    =D14    =E14    =F14    =G14
Person 16   Role 16 =B15    =C15    =D15    =E15    =F15    =G15
Person 17   Role 17 =B16    =C16    =D16    =E16    =F16    =G16
Person 18   Role 18 =B17    =C17    =D17    =E17    =F17    =G17
Person 19   Role 19 =B18    =C18    =D18    =E18    =F18    =G18
``````

Which would turn into:

``````Person 1    Role 1  Role 19 Role 18 Role 17 Role 16 Role 15 Role 14
Person 2    Role 2  Role 1  Role 19 Role 18 Role 17 Role 16 Role 15
Person 3    Role 3  Role 2  Role 1  Role 19 Role 18 Role 17 Role 16
Person 4    Role 4  Role 3  Role 2  Role 1  Role 19 Role 18 Role 17
Person 5    Role 5  Role 4  Role 3  Role 2  Role 1  Role 19 Role 18
Person 6    Role 6  Role 5  Role 4  Role 3  Role 2  Role 1  Role 19
Person 7    Role 7  Role 6  Role 5  Role 4  Role 3  Role 2  Role 1
Person 8    Role 8  Role 7  Role 6  Role 5  Role 4  Role 3  Role 2
Person 9    Role 9  Role 8  Role 7  Role 6  Role 5  Role 4  Role 3
Person 10   Role 10 Role 9  Role 8  Role 7  Role 6  Role 5  Role 4
Person 11   Role 11 Role 10 Role 9  Role 8  Role 7  Role 6  Role 5
Person 12   Role 12 Role 11 Role 10 Role 9  Role 8  Role 7  Role 6
Person 13   Role 13 Role 12 Role 11 Role 10 Role 9  Role 8  Role 7
Person 14   Role 14 Role 13 Role 12 Role 11 Role 10 Role 9  Role 8
Person 15   Role 15 Role 14 Role 13 Role 12 Role 11 Role 10 Role 9
Person 16   Role 16 Role 15 Role 14 Role 13 Role 12 Role 11 Role 10
Person 17   Role 17 Role 16 Role 15 Role 14 Role 13 Role 12 Role 11
Person 18   Role 18 Role 17 Role 16 Role 15 Role 14 Role 13 Role 12
Person 19   Role 19 Role 18 Role 17 Role 16 Role 15 Role 14 Role 13
``````

which is descending for each individual.

• I need more details. This is not clear enough to me on the point you are making. Specifically, I asking myself: What formula are you talking about? Is the grid you wrote supposed to be starting with A1 that has the value "1" or is A1's value "a"? Feb 16, 2012 at 17:19
• As stated, the numbers are the people going down column A. The letters are the roles going down column B. The formulas start at cell `c1` Feb 16, 2012 at 17:24

I came up with something that might work for you with some modification.

I have two sheets in my spreadsheet, 1 called Schedule, and 1 called Active. I put an "x" next to the person's name and date whenever they fulfill the role for that week or I enter someone else's name if they did it for the week. For example, here's the list of the past people that fulfilled the role for the week:

``````Carson  3/30/17 x
Devlin  4/6/17  x
Chris   4/13/17 x
Jake    4/20/17 x
Andy    4/27/17 x
Clifton 5/4/17  x
Jake    5/11/17 x
Hector  5/18/17 x
Carson  5/25/17 x
Chris   6/1/17  x
Andy    6/8/17  x
``````

Then I have a link the "Active" sheet on the "Schedule" sheet that references that data, looking for the date and the "x". Here's a picture since I can't figure out how to add Excel style text.

Hope that helps.