Random sort formula
where "names" is the named range B5:B11, "rand" is the named range C5:C11, and "sort" is the named range D5:D11.
How this formula works
This formula depends on two helper columns. The first helper column holds random values created with the RAND() function. The formula in C5, copied down is:
The RAND function generates a random value at each row.
Note: RAND is a volatile function and will generate new values with each worksheet change.
The second helper column holds the numbers used to sort data, generated with a formula. The formula in D5 is:
See this page for an explanation of this formula.
The formula in E5 is:
Here, the INDEX function is used to retrieve values in the named range "names", using the sort values in the named range "sort". The actual work of figuring out what value to retrieve is done my the MATCH function in this snippet:
Inside MATCH, the ROWS function is given an expanding range as the lookup value, which begins as one cell, and expands as the formula is copied down the column. This increments the lookup value, starting at 1 and continuing to 7. MATCH then returns the position of the lookup value in the list.
The position is fed to INDEX as the row number, and INDEX retrieves the name at that position.