Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Data Validation skips rows
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? |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Data Validation skips rows
"jk" wrote:
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range, Data Validation and Address, Match and Offset Funct | Excel Worksheet Functions | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Inserting rows with Data, Formula's and Validation | Excel Discussion (Misc queries) | |||
DATA VALIDATION IN REVERSE #2 (FOR JULIE D.) | Excel Discussion (Misc queries) |