Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

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.

share|improve this answer
    
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"? –  MADCookie Feb 16 '12 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 –  Raystafarian Feb 16 '12 at 17:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.