Generating random lists

  • Thread starter Thread starter Andy Sandford
  • Start date Start date
A

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
 
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)
 
You can also after generating the Random Values Select them Copy them
to same Range using Paste-Special Values. They want change afterwards.
HTH
 
Thanks very much for your quick responses - both solutions worked a treat!

Andy
 
Back
Top