ArviLaanemets, I tried yours and it works! Thanks for the concept; I may have a different use for it.



Back to IAmThatGuy's question, I think you answered it perfectly. However, I'm wondering whether having to dropdown each number is just making it rather fiddly for users.

Guy, I'm presuming that you are trying to make sure no numbers are skipped and no numbers are duplicated. Is there another reason you wanted to use a dropdown? I think from your example that your numbering will always be in regular sequence?

This is rather simplistic compared to Arvi's elegant response, but I have an issue with a coworker's worksheet for tracking items. He wants to hide the rows of projects that are completed, but sometimes because it's hidden, he ends up re-entering it, or reusing the record number.

I've applied a numbering column where the column's cells are locked, and where the numbers don't show up until he starts to type content in the next (B) column.

First number cell =IF(B2="","",1)
Subsequent number cells =IF(B3="","",MAX(A$2:A2)+1)
The latter, when filled down, becomes =IF(B17="","",MAX(A$2:A16)+1) etc.

I also apply conditional formatting to the column containing the project number, just your basic pink background/red text default one. So if he starts typing in a project that is either above, or better yet, on a hidden row, he's got a clue that he already has the information, and can check it. It doesn't prohibit him from entering it; but the next time he passes it to me to update it I can check why there are duplicates.

Thanks for this but this does not work if someone decides to insert a line in the middle of the data