Multiple condition data validation

  • Thread starter Thread starter IanC
  • Start date Start date
I

IanC

Is it possible to set up data validation to allow only numbers greater than
or equal to zero OR text equal to n/a.

I have check sheets for machine maintenance with specific tests listed and
cells formatted to accept results. These results are numeric (>=0) but there
are some machines without certain options fitted so some checks are not
relevant. I would like to allow the user to enter n/a in these cells if the
check is not required.

Any ideas?
 
Is it possible to set up data validation to allow only numbers greater than
or equal to zero OR text equal to n/a.

I have check sheets for machine maintenance with specific tests listed and
cells formatted to accept results. These results are numeric (>=0) but there
are some machines without certain options fitted so some checks are not
relevant. I would like to allow the user to enter n/a in these cells if the
check is not required.

Any ideas?
Click to expand...

You could use a formula for the validation:

=OR(AND(ISNUMBER(A1),A1>=0),EXACT(A1,"n/a"))

If you want to allow the n/a to be case insensitive, then try:

=OR(AND(ISNUMBER(A1),A1>=0),A1="n/a")

--ron
 
Yes you can.

Select Custom as the type and use this formula
=OR(AND(ISNUMBER(A1),A1>=0),A1="n/a")
 
Dear Ron & Paul

Many thanks for the suggestions. Sorted now using the non-case-sensitive
option.
 
Back
Top