Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number
Data Validation is an excellent way to control the entry of data in your worksheet to meet a certain condition. In this example, suppose you want to insure that numbers entered in the yellow cells are divisble by 5, as seen in the next picture. The following steps show how this can be done.
Step 1 of 5
Select the range of cells where you want your data entry validated.
Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.
Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.
Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =MOD(B3,5)=0
Step 5 of 5
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.
Note, this example validates numbers divisible by 5, but you can adjust the Data Validation formula to allow other divisible-worthy numbers. For example, if you want to only allow entry of numbers divisible by 3, your Data Validation formula would be =MOD(B3,3)=0.
THANK YOU
REGARDS
FROM ECUADOR ITS EXPLANATION VERY GOOD, THE DEVELOPMENT OF THE IMAGES EXCELLENT
I cannot for the life of me figure out how to modify the formula to work for my data validation. I have read the tutorial on Validating Data Entry for xDivisible Number which will create an error message is a number is not divisible by X. How do I write a validation formula to get an error when the number IS divisible by X?
For example, I do not want them to enter a number that is divisible by 100 or do not want a number rounded to the 100s.
Can you help?
The less complex question to answer is about a number being divisible by 100.
If your cell of interest is A1 for example, this custom validation would accomplish that:
=Mod(A1,100) followed by (because these html pages do not mix well with the) less than and greater than characters, followed by 0 to make the formula work like =Mod(A1,100) does not equal 0.
The “rounded by” part is more involved because data validation sees what the underlying value in a cell actually is, not what its visual rounded appearance is. You would need VBA for that, which is do-able but I would be careful if I were you because implementing that would essentially be contrary to what your objective seems to be which is to disallow only numbers divisible by 100. Rounding a bona fide number such as 137 down to its nearest 100 would trigger the disallowance by your standards. If I were you I would not do that. But it’s possible.
I was sure I tried the top solution, but must not have because it worked. Thank you so much for helping me solve my issue.