In this tutorial we will use Data Validation with lists, formulas and add a Form Control spinner button to limit the entry of numbers into a cell.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
In this exercise we need to control the entry of numbers into a cell. Our parameters are that the number be between 10 and 100, and be in multiples of 10.
We are going to use a couple variations of Data Validation to accomplish this. First, choose the List option:
We have a few options as to how we can enter the Source:
1) We can manually type in the values (e.g. 10,20,30,…)
2) We can choose a range if we have those numbers pre-entered in our workbook. In our case, we can enter =$A$4:$A$13 in the Source box.
3) We can also enter a named range, if we have one named. In our example, I’ve named cells B4:B13 as “Numbers”, so we can just enter =Numbers in the Source box:
And now we have a drop down list meeting our criteria:
Our users can select a number from the list, or manually type one in. If an incorrect number is typed, an error message will appear, asking them to Retry:
Our alternate method to control the entry based on our criteria that the number be between 10 and 100, and be in multiples of 10, is to use a formula. Again, there are three parameters we must meet:
1) The entry must be >=10
2) The entry must be < =100
3) The entry must be a multiple of 10. To accomplish this, we are going to choose Custom for our Data Validation option, and use the AND function, as follows:
We’ve entered 3 criteria in our AND function. The first two are straight forward, but we are using the MOD function to control the multiple of 10. The MOD function returns the remainder after the number is divided by the divisor. So in our case, the number is what is entered in E5, and the divisor is 10. As long as there is no remainder after that number is divided by 10, we have a TRUE result, and it then meets that criteria:
There is no drop down list, but our users can enter a value into the cell, and if it doesn’t meet the criteria, the same Retry message will appear.
Now, let’s add one more twist to our process. If we add a Spinner Button with the correct parameters to our worksheet, our users can just scroll through the list of numbers and not need to enter any.
To add a Spinner Button, go to the Developer tab, click Insert, and select the Spinner Button option from the Form Control section:
Then drag it into the area of your worksheet where you want it located. If you hold down your Alt key while dragging, it will fit cleanly into your row and columns dimensions:
Next, right-click on the Spinner Button and choose Format Control. A dialog box will appear where you can indicate the various parameters for the control:
Notice that we’ve set the minimum value to 10, the max to 100, listed the increment change as 10 and linked it to cell $E$5. Click OK and now you have an active spinner that scrolls through the numbers from 10 to 100 in increments of 10, and your users don’t have to manually enter anything:
Your users can use the Spinner Button or manually enter a number, and the Data Validation that we previously entered will still control those manual entries.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!