In the attached, tab 'Working'...

is there a way to:

• Add validation so duplicate entries are not allowed. Would normally use Data validation for this but this is already being used
  
• Restrict users from typing into column A. i.e. be forced to use the Drop down only
  

Thank you in advance

Attached Files

IDExample.xlsx (14.4 KB, 8 views)

Have you posted the same on other forums as you did previously?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Fair question.

No, I am sticking to this forum only. If that's OK

Your unique numeric entries are entered into column A of sheet Working. And probably you have a header (a string) for this column too.
On some hidden sheet (e.g. Hidden), into some cell (e.g. A1) enter formula, which calculates next unique value, like
Create a Name (e.g. nNextID), which refers to cell with formula
Activate any cell (e.g. A2) on sheet Working, and create a Name (e.g. nCurrID), like
I.e. the Name returns the value of column A of active row in sheet Working. Such Names are referred to as Dynamic Names.
Create a Name (e.g lIDList), like
For column A of sheet Working (for Datarange of this column), create a Data Validation List based on lIDList. Like
=lIDList

Now, whenever you activate this Data Validation List on sheet working, when the cell has any ID value entered previously, this value is only option, and when the cell is empty, the next ID value is the option.

You can also add another IF() level for lIDList, which checks is the active cell in column A, and when not, returns some error message instead ID value.

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, I have tried the steps but I am still able to enter duplicate values

and

enter text into the fields rather than only being allowed to select a value from the drop-down.

I have attached the updated sheet. Have I gone wrong somewhere?

Thanks in advance

Attached Files

IDExample.xlsx (16.1 KB, 6 views)

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

Have you set only Data Validation List values to be allowed (this is a default setting, but there is a possibility to change this)?
Anyway, I haven't access to MS Office until Friday, so I can't check out your attached workbook currently.

Yes, I have checked that under Data validation but that does not seem to be problem.
Appreciate if you are able to check? Thank you

shameless bump/ Any takers please?

Found the solution here" VBA to auto-generate a task ID (Unique and sequenced) | MrExcel Message Board