Generating a random set of numbers within Excel

**Phantomnz** · 03-26-2013, 09:43 PM

Hi Everyone,

I am pretty new to the forum and my knowledge is okay (30-40%) on excel, actually 20% knowledge...

I have a configurator that I use to generate an equipment Bill of Material, I want to create a Macro that when I open the configurator (Excel) it populates a random set of numbers into a cell. This number will be used as an Order Number of Reference number to the quotation/system build. This could be a 6 digit number sting, it needs to be different each time it opens. I have had no luck to date and was hoping someone with some experience could assist.

Thanks - look foward to the help.. Rhys

**majosum** · 03-26-2013, 09:54 PM

You could use the =rand() function. That will generate a random number between 0 and 1.

**FDibbins** · 03-26-2013, 11:42 PM

Hi and welcome to the forum

The =rand() will work for you, but it will change/update every time you do anything to the file (enter data into a cell, enter a formula, edit a cell etc). So if you only want it to change the 1 time (when you open the file) you will need to set calculation to manual.

However, this will have the disadvantage that NO other formulas will calc either, unless you press F9 - which will change your random number again
(just wanted to add some additional info for you)

**Phantomnz** · 03-27-2013, 05:34 AM

Thanks for the reply - I have used =RANDBETWEEN(0,5000) but the issue is as you mentioned it changes everytime I add an input into another cell.

How do I have it so its manual? All I want is a unique number when the excel is opened.

Cheers

**Phantomnz** · 03-27-2013, 09:10 PM

If anyone out there can help it would be greatly appreciated.

**FDibbins** · 03-28-2013, 12:35 AM

If you are using 2003, as your profile says...

select tools/options/calculation/manual

**Careless** · 03-28-2013, 03:50 AM

I SUGGEST you following solution

1> use a formula = large(sheet_quotation_ordernumber ,1 )+1 , when you actual fill data in quotation sheet )
2> 6 digit is not good for random , or your set a button to maintain auto increase ordernumber ,maybe more better , text( now(), "yyyymmdd_HHMMSS" )