Data Validation skips rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jk - 24 Jul 2006 10:51 GMT

I am using data validation to prevent duplicates in a column and the
validation i am using is =COUNTIF(A:A,A1)<2 .This works on some sheets but on
others, it skips every other two rows. What would cause this?

Reply to this Message

Max - 25 Jul 2006 09:15 GMT

> I am using data validation to prevent duplicates in a column and the
> validation i am using is =COUNTIF(A:A,A1)<2 .This works on some sheets but on
> others, it skips every other two rows. What would cause this?

Assuming the validation is applied correctly .. not sure, perhaps data entry
consistency? Eg: some text entries may contain "invisible" extraneous
whitespaces (leading, in-between, trailing) leading to non trigger of the
data validation for what looks to be duplicates

Try instead the validation formula:
=SUMPRODUCT((TRIM($A$1:$A$1000)=TRIM(A1))*(TRIM($A$1:$A$1000)<>""))<2
which allows use of TRIM, unlike COUNTIF

For calc efficiency, use the smallest range sufficient
to cover the max expected data entry extent in col A

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message