Validate in WS to prevent dual data entry

  • Thread starter Thread starter Jonah
  • Start date Start date
J

Jonah

How do I validate that the data entered in a worksheet is not a
duplicate from a list of 150 names. I have two columns with the same
list, and need a way to prevent dual entries.

Jonah
 
Jonah said:
How do I validate that the data entered in a worksheet is not a
duplicate from a list of 150 names.
I have two columns with the same list,
and need a way to prevent dual entries.
Click to expand...

Assume the 150 names are in col A
and col E is the 2nd input col

Select col E
Click Data > Validation
Allow: Custom
Formula: =COUNTIF(A:A,E1)<1
Click OK

Test it out ..

Perhaps a better alternative is to use a defined range,
eg: NList =Sheet1!$A$1:$A$150
(Defined ranges can be created via: Insert > Name > Define)

Then we could validate the "2nd" input col within any sheet
via the same steps above,

eg: assuming col E in Sheet3 is the "2nd" input col

In Sheet3,

Select col E
Click Data > Validation
Allow: Custom
Formula: =COUNTIF(NList,E1)<1
Click OK

---
 
Back
Top