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?

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top