PDA

View Full Version : Copy & paste test



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

Aussiebear
01-07-2011, 04:12 AM
The purpose of the above post, was to see if examples could be posted by using the cut & paste method of examples created and stored, dealing with issues raised in these forums, where a logical sequence is laid out for all to follow. Its a concept that I wish to explore in greater detail.