Here is one technique

First, ensure cell A1 is empty and goto ToolsOptions and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B10,COUNTIF($B$1:$B$2000,B1)=1 )),B1,INT(RAND()*2000+1))
it should show a 0

Copy B1 down to B2000.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B1, don't change it,
just edit to reset to 0, copy B1 down to B2000, and re-input A1.


Obviously, A1 can be any cell that you want.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ian" wrote in message
...
I work in a hospital and I have a worksheet of data from the patients we
treat. One column has their ID number and I want to add a column next to
this
to which I can add a series of random numbers as a security measure when
passing data to different departments. I have read a number of threads on
this site and feel comfortable in generating the unique random numbers
(thanks to Bernd Plumoff's UDF). But what I can't seem to manage is to
keep
this column of unique random numbers from updating, which defeats the
purpose. I realise I can just use RAND() and then hit F9 to turn this into
a
random number but I need to be sure sure that this rather tedious method
(when doing it for 2000 records) will not produce duplicate records.

I need to be able to generate a column of randomly assigned, unique
integers, in a number range that I can specify and that are not updated
once
they have been generated.

Thank you for any suggestions,
Ian.