Problems with -countif formula to prevent duplicate cells

[Legacy 360638]

I FOLLOWED ALL THE STEPS INDICATED ON YOUR AND OTHER WEBSITES AND FOR SOME UNKNOWN REASON IT DOES NOT WORK. I TRIED BOTH FORMULAS =COUNTIF(B:B,B1)=1 and =COUNTIF(B2:B500,B2)=1 THE SYSTEM STILL ALLOWS ME TO ENTER DUPLICATE ENTRIES.
PLEASE HELP!

 

[Tom Urtis]

Using all caps looks like you are shouting.

If you are entering a list of values that are meant to be unique, Data Validation can be applied to the cells, for example range A2:A20, so only unique entries are allowed.

Select range A2:A20 and then…
Press Alt+D+L on your keyboard to show the Data Validation dialog box.

In the Data Validation dialog box, select the Settings tab. From the Allow drop down list, select Custom.
In the Formula field, enter
=COUNTIF($A$2:$A$20,A2)=1

Note the relative and absolute references in the Custom formula.

Click onto the Input Message tab to enter optional text, such as entry instructions, that will appear when the user selects that cell.

Click onto the Error Alert tab and select "Show error alert after invalid data is entered". From the Style drop down list, select Stop, and in the Title and Error message fields, enter the text you want your users to see in the warning message that will appear when a duplicate entry is attempted.

Click OK to confirm your Data Validation settings.

 

[Legacy 360638]

Thank you so much for your immediate response. I apologize for the uppercase text. I had already sent it when I realized it was not proper.

I tried it but still does not work properly. I have the following columns: (A) staff initials, (B) studentid, (C) last name, (D) first name, (F) school. If I sort/filter the table by "staff initials, or "first name", the =countif formula works properly. However, if it is sorted by any other title it does not work.

 

[Tom Urtis]

Not being familiar with your workbook, I just tested this with various sorted fields and it worked just fine. I wonder if you entered the custom DV formula as I presented it, with relative and absolute references. I cannot duplicate the behavior you are seeing but I also do not see what is happening with your workbook design that may be influencing the problem.