Generating random lists

[Andy Sandford]

Hi all

I have generated a list of 16 random numbers with the RAND() function. The
list is then ranked, and the resulting data used to sort a table of names.

The problem I have is this...

I need to generate the list once, and then for it to remain unchanged.
Unfortunately whenever I subsequently enter data elsewhere in the workbook,
the list regenerates.

Is there any way I can create a series of 16 random numbers, that only
generates once?

Thanks in advance

Andy

 

[Bob Phillips]

Andy,

Here's one way.

Goto Tools>Options and on the Calculation tab click the Iteration checkbox
(this will suppress circular reference messages)

Put this formula in A1 and copy down to A16
=IF(OR(A1=0,B$1=""),RAND(),A1)

At this point they will still change, but put any value in B1 and they then
freeze. Change B1 to an available cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JMay]

You can also after generating the Random Values Select them Copy them
to same Range using Paste-Special Values. They want change afterwards.
HTH

 

[Andy Sandford]

Thanks very much for your quick responses - both solutions worked a treat!

Andy