Formula for Counting Data

[mlbdc2012]

Hello,

In my attached workbook, I'm trying to come up with formulas to fill in the data in the 3 Yellow Boxes of the Details Sheet inside my workbook.

I have three columns of data on this sheet, a number rank, a team and and X signifying what pick is currently up. In the yellow boxes, I would like cell G2 to read 2 because it's currently the 12th pick or the second pick of the second round. I'd like cell H2 to read 12 because it's the 12th pick, and I'd like cell L2 to currently read TEAM 9 because that's the team that is on the clock currently.

To find the round, I would like it to take into account the criteria on the 'Data' Sheet of my workbook. Simply have it devide the number of players drafted total by the number of rounds and that should tell it how many picks per round so it knows when to go up 1 round.

In addition, I'd like the list of numbers in column B to equal the number of total players drafted, if it can automatically produce those on the details sheet where they are currently located that would be terrific. I'd also like it to the same thing for the teams in column C. If 'Straight' Draft is shown in cell C6 of the Data Sheet then I would like teams 1-10 listed in order every ten teams or however many teams it says, if 'Snake' Draft is shown in cell C6 of the 'Data' Sheet I would like it to Snake around meaning the last team of each round would then draft first in the following round like it is shown on the 'Details' Sheet currently.

I'm sure there will be questions but this would be crucial to my project and I'm hopeful someone can assist me! Thanks for your time!

[samba_ravi]

see the attached file

[mlbdc2012]

see the attached file

What do leading spaces mean?

[samba_ravi]

In your original file

10 TEAM 10 X
11 TEAM 10 X
There is a extra space after TEAM 10 for serial no 11

[mlbdc2012]

In your original file

10 TEAM 10 X
11 TEAM 10 X
There is a extra space after TEAM 10 for serial no 11

Ok cool thanks.

Damn I forgot to put how does it know to calculate an X to switch to the next pick? I added a third sheet that needs to tell pick 12 when another team is entered in the picks sheet, anyway to do this so it knows to switch to the next pick?

[mlbdc2012]

Maybe something like if the sum of this =12 then an X goes in this cell. The sum would be the number of teams entered in column C of the 'Picks' Sheet.

[samba_ravi]

i didn't understood what you said

[mlbdc2012]

i didn't understood what you said

So in the worksheet I just attached, cell D14 of the 'Details' sheet needs to be told to put an X there so it can switch to the next pick. Using the 'Picks' sheet is there a way to tell D14 that there are now a total of 12 picks/teams listed in column C of the 'Picks' sheet. Cell D15 would then need 13 picks/teams in order to switch to the next pick. Does that make sense?

[mlbdc2012]

Any one have an idea on my last post?