Preventing duplicate entries within a range.
Here is a technique to prevent duplicate entries within a range (which is easy to do if you're entering, let's say, invoice numbers).
Suppose you want to prevent duplicate entries in the range A1:A10:Note: This material copyright of Rodney Powell Microsoft MVP - Excel, and I remain indebted to him for its use in this test
- Select cell A1 and choose the Validation command from the Data menu.
- Click the Data Validation dialog's Settings tab.
- In the Allow dropdown list, select Custom.
- In the Formula box, type:.
=NOT(OR(COUNTIF($A$1:$A$10,A1)>1))
- Now you'll need to set an alert style. Click the Error Alert tab.
- Set the Style dropdown to Stop (the alert message with a 'Stop' sign on it).
- In the Title box, type what you want to appear in the Error Alert's title bar (for example, "Duplicate Entry").
- In the Error message box, type the your text (for example, "This value already exists in the list").
- Click OK.
- Finally, back in the worksheet, copy cell A1 down through A10.