Multiple condition data validation

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?
 
R

Ron Rosenfeld

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
 
P

Paul C

Yes you can.

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

IanC

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

Top