Restrict cell content using data validation

Raymond21

New Member
Joined
Jun 8, 2016
Messages
3
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Hello Eric,

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

Just what I needed!

Very much appreciated.

=)
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top