SOLVED

Convert Calculated Value to a Static Value Automatically

Occasional Contributor

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?

 

 

3 Replies

Hello @jdkuehne 

 

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.

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.

best response confirmed by jdkuehne (Occasional Contributor)
Solution
I just found this brilliant solution to this vexing problem.
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html