Data Validation Multiple Criteria

[bobkap]

I have a column for users to enter a whole number. I only want to allow them to enter either a zero or any number >= 150. If they enter anything else they would get my custom error message.

I thought I could figure this out as it seems quite simple, but I just cannot. I think I need to use the "custom" in data validation and then enter the formula but that's where I get stuck.

Any help would be most appreciated.

 

[kevin9999]

You're almost there. When in Custom (assuming the first cell to have the validation is A1) type in the formula:

=OR(A1=0,A1>=150)

Then simply copy that cell to all cells where you want the validation to apply.

 

[Peter_SSs]

You're almost there. When in Custom (assuming the first cell to have the validation is A1) type in the formula:

=OR(A1=0,A1>=150)

That would allow, for example, both of the following which don't meet the limitations.
155.5
abc

I would suggest

=AND(A1=INT(A1),OR(A1=0,A1>=150))

 

[bobkap]

You're almost there. When in Custom (assuming the first cell to have the validation is A1) type in the formula:

=OR(A1=0,A1>=150)

Then simply copy that cell to all cells where you want the validation to apply.

MEGA thanks!

 

[bobkap]

That would allow, for example, both of the following which don't meet the limitations.
155.5
abc

I would suggest

=AND(A1=INT(A1),OR(A1=0,A1>=150))

Excellent point. It works perfectly. THANKS!!

 

[Peter_SSs]

Excellent point. It works perfectly. THANKS!!

You're welcome. Thanks for the follow-up.