Rowland wrote:
I have a much larger range but for the this example I only use 10 cells.
I need to pull random numbers from a range without repeating any of them
INDEX(A$1:A$10,RAND()*10+1
This works but it obviously is prone to picking repeats.How can I do
this with a formula without repeating..I can't use a VBA solution in
this spreadsheet.I can put a big clunky If in there to check each one
but theres got to be a cleaner way.


OK I think I have solved my problem with only adding one column
I add a column and enter Rand() down B1:B10(and then hide it)
In the C column where I need 10 non repeating random numbers from A1:A10
I use this formula
=INDIRECT("A"&RANK(B1,B$1:B$10))
It gets the Rank of the rand()number in B column(1 to 10) and uses that
with the indirect function to return a value from the A Column