Increment ID without duplication when a new row inserted

[Prescience]

I am building on from a table seating planner I recently found, and want to make the process as user friendly as possible for anyone not too familiar with Excel. I have a small issue that is driving me mad, as I can't seem to find the right approach to fix the problem.

Using the code below in a table, works fine, until you insert a new line or lines, which results in duplication of the UnusedID. I'd like to find a way for a user to insert new rows and the row number increment. Currently they have to manually select and drag to the bottom of the list to resolve any duplicated UnusedID numbers.

=IF(C2="--",SUM(MAX(D$1:D1),1),"") code works without issue until you 'insert' a new line.

Can someone provide an adjustment to the code above to ensure when a new row is entered we don't see duplicated UnusedID's as shown in the image below?

Thanks in advance for your knowledge and support.

 

[Sufiyan97]

Try

=IF(C2="--",ROWS($D$2:D2),"")

 

[Prescience]

Try

=IF(C2="--",ROWS($D$2:D2),"")

Thanks for the reply. This works at fixing the issue with incrementing the ID. However, it is missing the Sum(Max element and so fails.

 

[Sufiyan97]

In what way it fails?
What results it gives and what you want as result.
can you post some expected results? using xl2bb.

 

[Prescience]

In what way it fails?
What results it gives and what you want as result.
can you post some expected results? using xl2bb.

Sorry for the delayed response. I have been busy and not had a chance to explore further.

The code is missing the SUM MAX element that is required. This, while your code to insert row numbers works, the actual code fails. I am not sure how to describe any further and so have taken another route, which works, although not ideal.

 

[Prescience]

Original issue.

=IF(C2="--",SUM(MAX(D$1:D1),1),"") code works without issue until you 'insert' a new line.

Below is the solution.

=IF(C2="--", SUM(MAX(INDIRECT("D$1:D" & ROW()-1))+1), "")