Restrict cell content using data validation

[Raymond21]

Hello Everybody,

I need to restrict the entries of a cell to a range of numbers (i.e. 001 to 99) or the word N/A.


Meaning that only a number between that range or N/A can be entered in that cell. How can I do that using data validation in Excel?


Help will be very much appreciated

thanks.

 

[Eric W]

Welcome to the forum.

Let's say your cell is A1. Select A1, click Data Validation from the Data tab, select Custom in the Allow: box, and in the Formula box put this:

=OR(A1="N/A",AND(ISNUMBER(A1),A1<100,A1>=0))

Change the A1 to whatever cell you're in. You can also go to the Input Message tab and put a message saying what the valid values are.

Hope this helps.

 

[Raymond21]

Hello Eric,

Thank you very much for the help.

One things, though. I need to restrict the cell content so that people cannot put percentages. Let say 25% or 0.5%. Only whole numbers or decimals in between 0 and 100 like 0.25 or 0.05. Is there a way to add that restriction as well?


Thanks for your help.

 

[Eric W]

The tricky part is that to Excel, .5 and 50% are exactly the same value. The only difference is the formatting. If you have a cell formatted as General, and someone enters 50%, then Excel enters .5 as the value and changes the format to Percentage. So if you want to allow .5, but disallow 50%, you have to look at the formatting. There is one function that allows that (CELL), but it's a bit unusual. The documentation says that it's not supported in Excel Online, Excel Mobile, or Excel Starter. If that's OK with you, then try using this formula as the Data Validation rule:

=AND(OR(A1="N/A",AND(ISNUMBER(A1),A1<100,A1>=0)),LEFT(CELL("format",A1))<>"P")

Make sure the cell is already formatted as General or some non-percent number format. It works for me, but your mileage may vary. Let me know how it works.

 

[Raymond21]

Hello Eric,

Thank you very much for the help. It has worked wonderfully.

Just what I needed!

Very much appreciated.

=)

 

[Eric W]

Glad to help!