Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 82
Default Team Role Rotation (number of team members is variable)

In our company we have team meetings in each workgroup, and have a team role
rotation for those meetings. The number of members on each team is different.

What I'm trying to do is build a formula that looks at the list of members,
determines the length of the member list (counta) and then builds a rotation
list. The rotation list is just the list of members repeated say five times
over in the same sequence. I'll use that list to populate a matrix. Would
like to do this with worksheet functions if possible.

Any suggestions would be helpful.

Thanks in advance!

Scott

Example: member list
John Doe
Bill Smith
Susie Brown
Jack White

Example: rotation list
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Team Role Rotation (number of team members is variable)

I'm not sure what you are getting at, but here is another approach you
might like to consider:

Role_1 John Doe Jack White Susie Brown
Role_2 Bill Smith John Doe Jack White
Role_3 Susie Brown Bill Smith John Doe
Role_4 Jack White Susie Brown Bill Smith

After each meeting the members adopt their role plus one, with the
person in Role_max adopting Role_1.

Hope this helps.

Pete

Scott Wagner wrote:

In our company we have team meetings in each workgroup, and have a team role
rotation for those meetings. The number of members on each team is different.

What I'm trying to do is build a formula that looks at the list of members,
determines the length of the member list (counta) and then builds a rotation
list. The rotation list is just the list of members repeated say five times
over in the same sequence. I'll use that list to populate a matrix. Would
like to do this with worksheet functions if possible.

Any suggestions would be helpful.

Thanks in advance!

Scott

Example: member list
John Doe
Bill Smith
Susie Brown
Jack White

Example: rotation list
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 82
Default Team Role Rotation (number of team members is variable)

That's exactally what I'm trying to do. The only issue is that the number of
members in the workgroup may be 4, 9, 11, or any number. (less then 20
usually). The number of team roles is always 4.

How to I get that rotation, with the variable number of group members?

Can you help?

Thanks,

Scott

"Pete_UK" wrote:

I'm not sure what you are getting at, but here is another approach you
might like to consider:

Role_1 John Doe Jack White Susie Brown
Role_2 Bill Smith John Doe Jack White
Role_3 Susie Brown Bill Smith John Doe
Role_4 Jack White Susie Brown Bill Smith

After each meeting the members adopt their role plus one, with the
person in Role_max adopting Role_1.

Hope this helps.

Pete

Scott Wagner wrote:

In our company we have team meetings in each workgroup, and have a team role
rotation for those meetings. The number of members on each team is different.

What I'm trying to do is build a formula that looks at the list of members,
determines the length of the member list (counta) and then builds a rotation
list. The rotation list is just the list of members repeated say five times
over in the same sequence. I'll use that list to populate a matrix. Would
like to do this with worksheet functions if possible.

Any suggestions would be helpful.

Thanks in advance!

Scott

Example: member list
John Doe
Bill Smith
Susie Brown
Jack White

Example: rotation list
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Team Role Rotation (number of team members is variable)

Ok, well imagine you have the following covering A1:B11

Role_1 Person_A
Role_2 Person_B
Role_3 Person_C
Role_4 Person_D
Role_1 Person_E
Role_2 Person_F
Role_3 Person_G
Role_4 Person_H
Role_1 Person_I
Role_2 Person_J
Role_3 Person_K

This is for a team of 11 members. In C1 enter the formula:

=B11

and in C2:

=B1

Copy the formula in C2 down into C3:C11 by double-clicking the fill
handle (the small black square in the bottom right corner of the cursor
with C2 selected).

Now highlight cells C1 to C11, click <copy, then highlight cells D1:H1
(or however many rotations you need) and press <Enter.

There is your grid. The only difference for smaller groups is what you
enter in C1 initially (i.e. it should point to the last person in
column B for that size of group. You could have different groups in
different sheets, or leave a few blank rows between the groups and have
them arranged under one another.

Is this what you want?

Pete

Scott Wagner wrote:

That's exactally what I'm trying to do. The only issue is that the number of
members in the workgroup may be 4, 9, 11, or any number. (less then 20
usually). The number of team roles is always 4.

How to I get that rotation, with the variable number of group members?

Can you help?

Thanks,

Scott

"Pete_UK" wrote:

I'm not sure what you are getting at, but here is another approach you
might like to consider:

Role_1 John Doe Jack White Susie Brown
Role_2 Bill Smith John Doe Jack White
Role_3 Susie Brown Bill Smith John Doe
Role_4 Jack White Susie Brown Bill Smith

After each meeting the members adopt their role plus one, with the
person in Role_max adopting Role_1.

Hope this helps.

Pete

Scott Wagner wrote:

In our company we have team meetings in each workgroup, and have a team role
rotation for those meetings. The number of members on each team is different.

What I'm trying to do is build a formula that looks at the list of members,
determines the length of the member list (counta) and then builds a rotation
list. The rotation list is just the list of members repeated say five times
over in the same sequence. I'll use that list to populate a matrix. Would
like to do this with worksheet functions if possible.

Any suggestions would be helpful.

Thanks in advance!

Scott

Example: member list
John Doe
Bill Smith
Susie Brown
Jack White

Example: rotation list
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I set a macro to print variable number of pages in excel? Livio Excel Discussion (Misc queries) 1 July 30th 05 12:15 AM
sort list of players by team from player list on separate sheet Robert Excel Worksheet Functions 1 July 19th 05 01:57 AM
How do I assign a text value to a variable number of rows? Jday Excel Worksheet Functions 6 June 20th 05 01:46 PM
Chart based on variable number of columns Scott Hamilton Charts and Charting in Excel 2 June 5th 05 03:36 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 08:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"