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

[RJSIGKITS]

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!

 

[BrianMH]

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]

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]

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]

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]

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.