Data Validation

Posted by Wendy Smith on December 18, 2001 2:37 AM

I need to add data validation to a column so that the same data cannot be entered twice in that column (ie an invoice number can only be entered once).

Can anybody help please?

Posted by Alix on December 18, 2001 2:51 AM

Hi Wendy

To restrict a range to unique values where the range is A1 to A100:

Go to : Data -> Validation

From the 'Allow' drop down select 'Custom'

A formula box appears

In Formula type the following:

=COUNTIF($A$1:$A$100,A1)=1

hit OK

This will flash an error message if the same value is entered twice and will not allow the second entry.

HTH!
Alix

Posted by Paul C on December 18, 2001 8:45 AM

I'd like to take the question one step further.

I have a list of "valid data inputs" and I want to prevent the user from picking the same value twice from the list.

Any ideas of how to do this?

Posted by Aladin Akyurek on December 18, 2001 11:34 AM

Paul --

You have apparently a range, say A1:A25, filled in with "valid data inputs" and you want to prevent user to enter anything that is already in A1:A25. If this is the correct interpretation, then

activate A26 (which is yet empty),
activate Data|Validation,
Choose "Custom" for Allow,
Enter the following as formula:

=COUNTIF($A$1:A25,A26)=0

click OK.

Copy down cell A26 as far as you need.

If this is not what you looking for, please elaborate on your question.

Aladin