Automatic Generation of record numbers

[DPChristman]

I am trying to create a spreadsheet of records that will automatically numerate cases.

There will be up to 1,000 records created annually.

Our existing process is a =MAX +1 formula, which requires numbers to be entered manually for each case (and is subject to human error)

Normally this would be an easy thing to do with a simple formula. However, there is a catch.

As the data is entered over time, the spreadsheet may be sorted by any number/combination of 85 columns.

If I was the only user, I could create an easy work around by converting previous case numbers to values only.

However, there will be multiple users of the spreadsheet, and most have very limited PC and excel skills.

Accidentally reassigning a case number to the wrong entry is an unacceptable risk.

Any thoughts?

 

[KenDue]

Could you have a text box named "Add New Record" with a Macro assigned to it that would go to the next blank row, type in your formula of =MAX+1, then copy and paste the result back over the formula "As Value"?

All your numbers would be values, and less susceptible to inadvertent changes.

 

[JLGWhiz]

If the document numbers do not have to be absolutely consecutive, but remain sequential, then you could use something similar to the military logistics document numbering method.

Range("A1") = Format(Now, "YYMMDDHhnnss")

give you a number that looks like this: 161125171915, and won't be duplicated for the next hundred years. You could set it up with a worksheet selection change event macro that would assign the number when a user clicks in the document number box. The military uses a Julian date plus four digit serial number which they restart daily because it only gives them 9999 unique daily numbers.[TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl64, width: 133, bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]