Data Validation for Specific Values

Original Question:-

How to enter between specific values through Data validation?

In my assignment I have been asked to use data validation to restrict a user from entering a value that is outside the range ">0" and "<=500".

I have no idea how to accomplish this. I can't use the between option because then the user could type in 0. What other options are there?

There are two ways you can allow user to enter values between 0 & 500

  • Select the range where you want data validation to be applied
  • Press ALT + D + L shortcut keys
  • From Settings tab click on Allow drop down & select Whole
  • From Data drop down select between & enter the minimum as 1 & maximum as 500

image 1


The second way is to get solution through AND function

We need to follow the below steps:

  • From Settings tab click on Allow drop down & select Custom
  • In Formula box use =AND(A1>0,A1<=500)

image 2


Both ways will allow values between 1 & 500; if we enter any other value then an error message will appear.

In this way we can Data validation to allow user to enter between specific values.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.