1 - I see you could change the formula to have the experssion COUNTIF(\$C\$1:C1, \$E\$2:\$E\$5)<5 changed so the maximum number of team members looks to a cell (say E9) so the number of people per team could be easily changed eg COUNTIF(\$C\$1:C1, \$E\$2:\$E\$5)<E9

2 - In (virtual) sailing team racing, competition is usually 3 vs 3, but could be adjusted to a 4 vs 4, 3 vs 2 or 4 vs 3 depending on the number of skippers available to race. Could the example be modified to assign a skipper to a team given these optimum arrangements? (eg for say 12 skippers entered it would do 2 sets of 3 vs 3, for 8 skippers it would do one 4 vs 4, etc)

Array formula in cell C2:

=INDEX(OFFSET(\$D\$2:\$D\$5, 0, 0, \$F\$1), LARGE((COUNTIF(\$C\$1:C1, OFFSET(\$D\$2:\$D\$5, 0, 0, \$F\$1))<\$F\$2)*(ROW(OFFSET(\$A\$1, 0, 0, \$F\$1))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF(\$C\$1:C1, OFFSET(\$D\$2:\$D\$5, 0, 0, \$F\$1))<\$F\$2))))) + CTRL + SHIFT + ENTER.

Copy cell C2 and paste it down as far as necessary.

Change values in cell F1 and F2 to create a different team setup.

Dynamic Team Generator.xls
(Excel 97-2003 Workbook *.xls)

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

LARGE(array,k)
Returns the k-th largest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

RANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify