Excel Puzzle - Eliminating Random Numbers in a Diminishing Sequence

[gjohn282]

Good Afternoon Everyone,

I'm trying to figure out how to take advantage of Excel's random (rand) function in order to come up with a formula to solve this puzzle...

Puzzle: You have 9 pieces of paper, each with a different number on it ranging from 1 to 9. There are no repeat numbers; simply each paper as the either 1, 2, 3, 4, 5, 6, 7, 8, or 9. You randomly select one of the pieces of paper, and record the number in cell A1. You repeat this process in cells A2 through A9. Your sequence of numbers is completely random. How can I mimic this in Excel?

The formula in cell A1 is easy enough: =RANDBETWEEN(1,9)

What would I put in cells A2 through A9 so that it eliminates the number(s) that have already been used? I gave up after a few days of trying to figure this out and started building tables to put together a process of elimination, but still haven't quite figured it out. I'm sure there's an easier way. Thanks!

Note: I do want this to be completely random, so that I can use the F9 key and record various outcomes.