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