Sep 29 2022 11:15 AM
Sep 29 2022 11:15 AM
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?
Sep 30 2022 12:30 AM
as you are using Microsoft 365, have you ever thought about using the Forms app for this purpose (https://www.office.com/launch/forms) ?
It's easy to create forms there which could be used on any device (also mobiles) and the best thing is, all the answers are automatically stored in an Excel workbook as a table. So whenever you need, you could download the workbook and do your analysis in Excel.
Sep 30 2022 07:01 AM
Thanks for your response @Martin_Weiss.
I looked at this initially, but I didn't understand how to create what I needed using this tool, so I went with Excel. This form number is the last puzzle piece needed to complete the form.
Sep 30 2022 08:45 AMSolution