Ok.. The title is horrible but I couldn't figure out how to phrase what I'm looking for.

Let me explain what I'm trying to do.

I'm trying to put together a spreadsheet for a baseball season ticket draft that I have coming up. Currently we have 5 people though this could change in the draft. There are 83 games, this could fluctuate up and down also. I need a way to determine where to put people in the drat order.

Still confused? I am. Here is what I'm currently doing by hand.

83 Games

Josh - 30 games - One pick every 2.7 turns

Jim - 20 Games - 4.15

Mike - 18 Games - 4.61

Bill - 10 Games - 8.3

Frank - 5 Games - 16.6

What I would like to do is have a list of people in the league in a column and the number of games in the next.

A C

Josh 30

Jim 20

Mike 18

Bill 10

Frank 5

I would like the spreadsheet to go down and place the people where they should pick. One Caveat, Mike ALWAYS picks first.

1. Mike

2. Josh

3. Jim

etc.

Does that make sense at all? Am I trying to do too much?

Thanks

Josh

The algorithm for the first person (Mike) is:

If it's the first pick, then it's their turn, and they start round 1

otherwise, it's 1 + (round * pickevery) -- then increment their assignments and their round

The algorithm for everyone except the first person is :

if the Pick = Round * PickEvery, then they get a pick -- then increment their assignments and their round

So, the batting order looks like this:

Order Name Picks First Pick Picks Every

1 Mike 18 1 5

2 Josh 30 3 3

3 Jim 20 4 4

4 Bill 10 8 8

5 Frank 5 17 17

Pick order starts looking like this:

1. Mike : starts #1, his round 1 is complete, his next pick is: 1+(1*5) = 6

2. ??? (no one)

3. Josh (starts #3, his round 1 is complete, his next pick is round 2 * 3 = 6

4. Jim (starts #4, his round 1 is complete, his next pick is round 2 * 4 = 8

5. ??? (no one

6. Mike (1 + (round-1)*5 = 6), where Mike is in round 2

7. Josh (round * 3 = 6), where Josh is in round 2

8. Jim (round 2: 2*4 = 8)

9. Bill (first pick is #8)

...

and so on...

See attached: You make changes to the Yellow area... add more rows underneath if you want -- (it's a dynamic named range there...

Run the code in Module 1... it will Put the pick order on the Sheet named "Picks"

SeasonPassPicks.xls