How to generate a random number which becomes fixed upon a selection from Data Validation

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
Hi Guys.
What I am trying to achieve:
A Single form to create quotes and orders, with a cell that generates a random quote number each time the form is amended. The quote number must be different if an amendment has been made to the document.
The quote number needs to then become a static fixed number once the status of the quote is amended to "Ordered", and absolutely must not be able to change after the form has been converted to an order.
The order number must match the last quote number before it was converted to an order.


What I currently have:
Data validation in cell C51 which is the "Order Status"
The Drop down options within the Data Validation are: "QUOTED", "ORDERED", and "DELIVERED" - I may add more options to this in the future.
Cell D6 is to return the result by way of a randomly generated quote number between the numerical ranges of 100000 - 199999
=RANDBETWEEN(100000,199999) is good for the quotes in that it will consistently refresh itself each and every time an amendment is made, thus creating different quote numbers for each quote amendment, which is all good.
The problem with RANDBETWEEN, is precisely that as well, though. For once I change the selection in C51 to "ORDERED", I need the random number that has been generated in D6 to then become fixed, and to no longer change, thus providing an order number which matches the last quote number before being turned into an order.

Any help would be massively appreciated! :eek:
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
Random numbers aren't all that great for this sort of things. What happens when the random number is duplicated 3 times? It would be best to have the number generate in sequence.
 

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
Okay, I get your point here. that would be perfectly suitable and does make sense to do this, but still needs to generate itself each time an amendment is made to the document up until the point at which the Data Validation is amended to "Ordered", which would then need to freeze the last number whilst it was still a quote.
 

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
Or you could just leave it blank and generate a number once it is set to ordered and then lock the cell and save the sheet.
 

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
Thanks for your suggestions, BrianMH, but that's really not what I'm after.
I need other users to be able to use this sheet, and it all needs to be automated. I really need to find a way of making it do what I need it to do...
 

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
What your doing would be better suited to a database. Do you have access available? It is much better for a multiuser environment.

Any way you generate a number if there is no record locks of any sort you will end up with duplicate order numbers because everyone's spreadsheet isn't reading everyone else's spreadsheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,768
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top