Prevent random numbers from changing?

[GHI1968]

Start from scratch.xlsx


I would like to simply rename a copy of this sheet each week to week 15, week 16 etc.

At the moment I have it to generate random numbers for a reference (Its only a very simple check if there are any queries and doesn't need to be anymore than a random number)

When I copy and create a new week I would like it to create new reference numbers once only then keep those numbers and NOT update them each time the sheet is opened...... is this possible?

I would like to keep it very simple so anyone can create a new week without having to let them have passwords, change formula etc.

[jason.b75]

In T5 and filled down

=IF($T$2="",RANDBETWEEN(10000000,99999999),T5)

The random numbers will be locked when anything is entered into T2 and refreshed when it is blank.

Might need to enable iterative calculation on excel options.

Does that help?

[GHI1968]

Is it possible to lock the reference numbers in rows in column T if anything is entered into the same row in column B? Or even lock all the reference numbers in the whole of column T if any information is entered into any cell in column B??

[jason.b75]

Try changing the condition of the logical test.

To lock individual rows as column B is filled.

=IF($B5="",RANDBETWEEN(10000000,99999999),T5)

To lock the whole column when one row in column B is filled.

=IF(COUNTA($B$5:$B$86)=0,RANDBETWEEN(10000000,99999999),T5)

[GHI1968]

Thank you Jason. Thats one more problem solved :-)

[GHI1968]

It stopped the numbers changing as soon as I type anything into T2 and worked fine until I save and re-open the sheet and I get a warning about a circular reference?

[jason.b75]

Easy to fix

File > Options > Formulas

Tick the box to 'enable iterative calculations'

[GHI1968]

Fixed! Thank you Jason

[hjalli]

I found I needed persisted random numbers and/or reproducible random number sequences all the time. Turns out this is possible using only standard spreadsheet formulas.

One way is to use the so called Lehmer random number method. It generates a sequence of random numbers in your spreadsheet that stays the same until you change the "seed number", a number you choose yourself and will recreate a different random sequence for each seed number you choose.

The short version:

1. In cell B1, enter your "seed" number, it can be any number from 1 to 2,147,483,647
2. In cell B2 enter the formula =MOD(48271*B1,2^31-1) , this will generate the first random number of your sequence.
3. Now copy this cell down as far as the the random sequence you want to generate.

That's it! If you want a different set of numbers, just change the seed number in B1. If you ever want to recreate the same set of numbers again, just use the same seed and the same random sequence will appear.

Note: The random numbers will be in the range 1 to 2,147,483,647. To normalize them to the range 0 to 1 as the results of the built-in RAND function, go to cell C2 and enter the formula =B2/(2^31-1). Then fill that formula down as far as your random number sequence in column B.