Data Validation Not Working

[ANE0709]

I am trying to add a data validation to A1-A20000 to exclude "-" and "-0" from being entered into any cell but it isnt working. Can someone please point me in the right direction? I have the Error Alert as well but that isnt triggering either.

 

[Fluff]

How about

=ISERR(FIND("-",A1))

 

[ANE0709]

How about

=ISERR(FIND("-",A1))

this worked. thank you

I had tried =iserror(find("-",A1)) previously but that didnt work either. Any idea why "iserr" would work but not "iserror"?

 

[Fluff]

You're welcome & thanks for the feedback.

ISERROR should work just as well

 

[LegendaryAxe]

this worked. thank you

I had tried =iserror(find("-",A1)) previously but that didnt work either. Any idea why "iserr" would work but not "iserror"?

The issue is the OR function, not ISERROR. Using OR in this case will return TRUE as long as one of the values is NOT in the cell. So, if the cell's value is "-", the OR logical formula will return TRUE (since it will not find "-0" and thus return an error), even though you would want a FALSE as this is one of the values you do not want in the cell.

In this particular case one value contains the other ("-" is in "-0"), so simply using FIND("-",A1) would work to catch any undesirable values.

See below. I created a different OR logical test that will work, but again, in this particular instance, the ISERROR(FIND("-", A1)) formula is what you would want to go with as it's simpler and does the same thing.

Book1
	A	B	C	D	E
1	Validation Text	New OR logical test	Old OR logical test	Testing for just "-"	Testing for just "-0"
2	"-0"	FALSE	FALSE	FALSE	FALSE
3	-	FALSE	TRUE	FALSE	TRUE
4	Text	TRUE	TRUE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=NOT(OR(ISNUMBER(FIND({"-0","-"},A2))))
C2:C4	C2	=ISERROR(OR(FIND("-",A2),FIND("-0",A2)))
D2:D4	D2	=ISERROR(FIND("-",A2))
E2:E4	E2	=ISERROR(FIND("-0",A2))