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?

 

[thp510]

Even though your formula is in B4, you need to put A1 in it, instead of A4. Or this might be a little clearer:

Book1
	A	B	C	D
1
2
3	Account	Recommended Owner
4	aaa	Name 1		Name 1
5	bbb	Name 2		Name 2
6	ccc	Name 3		Name 3
7	ddd	Name 4		Name 4
8	eee	Name 5		Name 5
9	fff	Name 6		Name 6
10		Name 7		Name 7
11		Name 8		Name 8
12		Name 9		Name 9
13		Name 10		Name 10
14		Name 11		Name 11
15		Name 12		Name 12
16		Name 13		Name 13
17		Name 14		Name 14
18		Name 15		Name 15
19		Name 16		Name 16
20		Name 17		Name 17
21		Name 18		Name 18
22		Name 19		Name 19
23		Name 20		Name 20
24		Name 21		Name 21
25		Name 22		Name 22
26		Name 23		Name 23
27		Name 23
28		Name 22
29		Name 21
Sheet25

Cell Formulas
Range	Formula
B4:B29	B4	=INDEX($D$4:$D$99,ABS(IF(MOD(ROWS(B$4:B4)-1,COUNTA($D$4:$D$99)*2)<COUNTA($D$4:$D$99),-1,COUNTA($D$4:$D$99)*2)-MOD(ROWS(B$4:B4)-1,COUNTA($D$4:$D$99)*2)))

Amazing! Thank you again.