Select unused number
 

I have a list of numbers (1 to 9) in G1:G9 that are displayed in a random
manner, with no duplicates.

In G2 I need to put a random number from 1 to 9 Other than the number in G1.
In G3 the number has to be one of the remaining 7 numbers.
in G4 the number has to be one of the remaining 6 numbers.
etc.
I don't want to use a macro to do this, just functions and not UDF, either.

Any suggestions would be appreciated.

drbobsled.

Create a 9 row vector with the name Randy. Fill with
=RAND()
Create an adjacent 9 row vector named Sandy. Fill with
=RANK(Randy,Randy)
Hit F9 until the first row of Sandy is equal to G1.