I have a table in excel with samples ID, provenance etc. They were each given a sample ID with an AD prefix and 5 digits, the first 2 based on the year, and the last three numbers growing from 1 to 999.
The samples come from different regions, or owners, and users like to keep samples from same user or provenance together so that the table numbering is a bit "haphazard" with the last entry in the table is not necessarily the last entry made, and therefore, not the largest number.
I would like to have the new ID number automatically generated in the first empty row at the bottom of the table so that when a new sample is entered it can automatically get its new number, and if the user wishes, the row can be cut and pasted within the table with samples from same location or customer, and the next ID number generated for the next sample.
I think that a macro would be easier as with a formula, the number generated might change if the row is moved to another spot.
I am adding a mini table as an example which will clarify what I am looking for?
Adding a help column in the table could be added.
Thanks for the help!