Trying to create a snake draft

[thp510]

I have a list of 3 individuals that I would like to assign using a snake draft (similar to a fantasy football style draft), where the the last person to pick something is the next person to pick. For example, there are 3 people (Amy, John, Bob) and here's their pick order: Amy goes 1st, John goes 2nd, and Bob goes 3rd. Here's the first 12th pick order.

1st Pick: Amy
2nd Pick: John
3rd Pick: Bob
4th Pick: Bob
5th Pick: John
6th Pick: Amy
7th Pick: Amy
8th Pick: John
9th Pick: Bob
10th Pick: Bob
11th Pick: John
12th Pick: Amy

You get the picture. I want to create an equation where I can just populate the first 3 names and then drag the rest of the cells down the rows.

What equation would I put in cell C4?

 

[Fluff]

How about
=CHOOSE(MOD(ROW(A1),6)+1,$C$3,$C$3,$C$2,$C$1,$C$1,$C$2)

 

[thp510]

Great! It worked. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback

 

[thp510]

How about
=CHOOSE(MOD(ROW(A1),6)+1,$C$3,$C$3,$C$2,$C$1,$C$1,$C$2)

Hi Fluff... in an effort to understand your equation, how did you think about the $C$3,$C$3,$C$2,$C$1,$C$1,$C$2 portion of the equation when using the Choose function?

 

[thp510]

And as a follow on, if I had 8 people instead of 3, I'm guessing I would change the "6" to 16 (double my pool of 8 folks)? How do you make out the last part of the function if I had 8 people.

 

[Fluff]

I'm guessing I would change the "6" to 16 (double my pool of 8 folks)?

That's right
The first two values in the list must be the last two names (C$8,C$8, for 8 people) then the next has to C$7, followed by C$6 etc until you get to C$1 at which point you come back down the list until you have 16 values to choose from

 

[thp510]

Brilliant! Thanks so much.

 

[Fluff]

My pleasure

 

[thp510]

Fluff, sorry. One last time... I tried doing your approach for 8 and I'm stuck on the last part of the equation. I did (C$8, C$8, C$7, C$6, C$5, C$4, C$3, C$2, C$1, C$1, C$2, C$3, C$4, C$5, C$6, C$7). Those are the 8 folks doing the snake and it's the order I want. However it's not properly doing the snake. Thoughts?