I'm not sure if this is even possible, but I am working on creating a sales form template for use at trade shows. The idea is that a salesperson will launch a blank form template on a tablet and fill it in with the customer's info and order, and then save the new order to the cloud so it can later be added into a database system for processing, or even reopened and amended. This is for Excel 365 for desktop on a Surface Pro 8.
Ideally, the template would be locked, forcing the user to save to a new filename. I'd like to give each order form a unique, auto-generated serial number, sequentially if possible, but even a random number is okay as long as it is persistent and doesn't change every time the worksheet recalculates.
I know I could flatten a generated value, but most users will not know how to do this, let alone remember to do it each time. Is there a function, a script, or some other way of doing this?
I posted this question to Reddit a few weeks ago, but nobody came up with a working solution. The best suggestion I got was to use the NOW() function in a VBA script to generate a unique number based on date and time. This is fine, but I believe the script will need to check for an existing value in the target cell and not paste a new value if one exists. So, the first time the NOW() function runs it will paste the value, but subsequent sheet calculations would be prevented from updating the target cell. I have not been able to make this work.
Is there a way of doing this, either through a formula or a script?
