Microsoft Office Forums - View Single Post - [Solved] multiple validation on a column to avoid duplicate entries

IamThatGuy · 02-23-2022, 08:49 AM

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

Quote:

Originally Posted by ArviLaanemets

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

Code:

=MAX(Working!$A:$A) +1

Create a Name (e.g. nNextID), which refers to cell with formula

Code:

nNextID = Hidden!$A$1

Activate any cell (e.g. A2) on sheet Working, and create a Name (e.g. nCurrID), like

Code:

nCurrID = Working!$A2

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

Code:

lIDList = IF(nCurrID = "", nNextID, nCurrID)

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.