Originally Posted by ArviLaanemets

1. On hidden sheet, create a formula which calculates next ID (Finds largest numeric part used, and adds 1. E.g. when you have ID values from "X001" to "X010", then net ID will be calculated as "X011".);
2. Create a dynamic named value, which returns the value of ID column in currently active row in your data table (E.g. when you have activated the row with ID "X002", then the dynamic value will be "X002", when you have activated the row without ID, the dynamic value will be "".);
3. I hidden sheet, create another formula, which returns either dynamic value from p.2 when tis is not empty string, or next ID calculated according p.1. And create a named value which returns the result or formula. (Another option is to create a dynamic name directly, without 2nd formula on hidden sheet, but there may be problems with this when you use some of latest versions of Office!);
4. In your datasheet, create data validation list for ID column with named value from p.3 as source. Now, whenever you activate data validation for existing ID, the same ID will be only available selection. And whenever you activate data validation for row with empty ID, the new ID will be only available selection. And any attempts to enter manually an ID not matching the one provided by data validation, will be blocked (only workaround is pasting the ID value into ID column - obviously this is a design flaw in Excel!).