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! :eek: :eek:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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!