With this 'autonumber' calculated with formula, whenever the table is sorted, those 'autonumbers' get attached to different set of data!
I have had a couple of cases where there was need to have such unique ID column, and my approach was:
On your entry sheet, add a hidden column, where the numeric part of your autonumber is calculated, like
Code:
=IF(LEFT($A1,5) = "REQ-",1*MID($A1,5,254),"")
Define a name like
Code:
nNextNumber = "REQ-" & RIGHT("0000" & (MAX(YourDataSheet!TheColumnWithEntryNumber) +1),4)
Activate some cell on your data entry sheet (e.g. A1), and define a name
Code:
lAutoNumber=IF(YourDataSheet!$A1 <> "", YourDataSheet!$A1, IF(OR(YourDataSheet!$B1 = "", LEFT(YourDataSheet!$B1,7)="Section"),"",nNextNumber)
On your data entry sheet, for column A define data validation list with source
When user enters anything into column B which doesn't start with "Section", and there is no autonumber in column A, then user can select new autonumber.
This set-up will work best, when you define your data entry table as a Table - then all formulas and data validation lists are applied automatically to new rows added to Table.