Inserting UNIQUE entries
Use DATA VALIDATION to restrict user input into a cell or a range of cells.
If you have to enter information into a range of cells on a worksheet, it might be that that entry has to be a UNIQUE value.Perhaps the best example is entering an invoice number. That number must be UNIQUE. In Excel you can use Data Validation to force a user to never enter the same invoice number twice. How does it work?
- Select the range of cells where the UNIQUE invoice numbers (entries) must be entered. For example B6: B10
- Click on the Data tab and choose: Data Validation > Data Validation …
- Click the Settings tab of the Data Validation dialog
- For Allow: choose Custom
- Enter the following formula at Formula: = COUNTIF($B$6:$B$10; B6)<2
- When required, fill in the Input Message and Error Alert tabs
- Click OK
If you enter an invoice number that already exists in one of the cells in B6: B10, Excel will not allow it and will inform you about it!
Click here for a Dutch version of this tip.