Problem:
We want to make it impossible to enter duplicate values in List1 (cells A2:A10).
Solution:
Use Data Validation as follows:
1. Select the range to validate (cells A2:A10)
2. Data --> Data Validation --> Settings
3. From the Allow dropdown list, select Custom.
4. Enter the following COUNTIF formula in the Formula box:
=COUNTIF($A$2:$A$10,A2)=1
5. Select the Error Alert tab.
6. In the Title box, enter "Duplicate Entry".
7. In the Error Message box, enter "The value you entered already appears in the list above."
8. Click OK.
After following this procedure, any duplicate entry will cause the error message to pop up, forcing the user to enter a different value.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
