paulgunther
New Member
- Joined
- Oct 30, 2019
- Messages
- 31
- Office Version
- 365
- Platform
- Windows
I have a spreadsheet that assigns players to random holes (Start) based on a chosen number of players per card (I3). It's fairly simple and I also have a macro assigned to a button that copies the "random" column and pastes values to the "static" column to re-randomize at will. The "hole_assignment" table is to assign the holes in a specific, non-sequential order. This works fine as designed.
Question: I would like to occasionally assign certain players to specific holes (column A) and have the Start column account for the pre-assigned starting hole and randomize the rest.
Example: Player 2, pre-assigned hole 8, Player 6 pre-assigned hole 8. I now need the randomizer to only assign 2 additional players to hole 8 and automatically place those players on their respective pre-assigned holes.
Question: I would like to occasionally assign certain players to specific holes (column A) and have the Start column account for the pre-assigned starting hole and randomize the rest.
Example: Player 2, pre-assigned hole 8, Player 6 pre-assigned hole 8. I now need the randomizer to only assign 2 additional players to hole 8 and automatically place those players on their respective pre-assigned holes.
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3:C42 | C3 | =VLOOKUP([@Group],Hole_Assignment,2) |
D3:D42 | D3 | =CEILING(RANK([@Static],[Static])/$I$3,1) |
E3:F42 | E3 | =RAND() |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A3:A42 | Expression | =COUNTIF($A$3:$A$42, A3) = 2 | text | NO |
A3:A42 | Expression | =COUNTIF($A$3:$A$42, A3) = 3 | text | NO |
A3:A42 | Expression | =COUNTIF($A$3:$A$42, A3) = 4 | text | NO |
C3:C42 | Expression | =COUNTIF($C$3:$C$42, C3) = 2 | text | NO |
C3:C42 | Expression | =COUNTIF($C$3:$C$42, C3) = 3 | text | NO |
C3:C42 | Expression | =COUNTIF($C$3:$C$42, C3) = 4 | text | NO |
C3:C42 | Expression | =COUNTIF($C$3:$C$42, C3) = 5 | text | NO |
C3:C42 | Expression | =COUNTIF($C$3:$C$42, C3) > 5 | text | NO |
B3:B42 | Cell Value | duplicates | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B2 | Any value | |
C3:D42 | Any value |