Aussiebear
01-07-2011, 03:47 AM
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:
Select cell A1 and choose the Validation command from the Data menu.
http://www.beyondtechnology.com/img/tips020a.gif
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))
http://www.beyondtechnology.com/img/tips020b.gif
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").
http://www.beyondtechnology.com/img/tips020c.gif
Click OK.
Finally, back in the worksheet, copy cell A1 down through A10.Note: This material copyright of Rodney Powell Microsoft MVP - Excel, and I remain indebted to him for its use in this test
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:
Select cell A1 and choose the Validation command from the Data menu.
http://www.beyondtechnology.com/img/tips020a.gif
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))
http://www.beyondtechnology.com/img/tips020b.gif
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").
http://www.beyondtechnology.com/img/tips020c.gif
Click OK.
Finally, back in the worksheet, copy cell A1 down through A10.Note: This material copyright of Rodney Powell Microsoft MVP - Excel, and I remain indebted to him for its use in this test