In this tutorial we're going to look at how Santa is using Excel Custom Data Validation to limit the number of presents kids request on their Christmas wish list.
With Christmas fast approaching our kids are already talking about what Santa might bring them this year.
Little do they know that Santa will be implementing a new, 3 present request limit and formalising the process via a spreadsheet on Excel Online.
Yes Kids, Santa is finally tech savvy!
Below is a preview of the form. Watch what happens if you try to list more than 3 presents:
How did Santa set this up? Let’s just say he had a little helper 😉
Custom Data Validation
Custom Data Validation is actually quite straight forward and very flexible.
Step 1: Select the range of cells you want validated. Mine are C8:C17… I’ve allowed more than 3 cells because I expect some tenacious kids will not stop at the first error they get in C11!
Step 2: Go to the Data tab of the ribbon > Data Validation > Data Validation
Step 3: In the Settings tab choose ‘Custom’ from the ‘Allow’ list:
Step 4: Enter your formula.
The formula must evaluate to TRUE or FALSE. i.e. it’s a logical test. My formula is:
=COUNTA($C$8:$C$17)<=3
Which simply counts how many cells in the range C8:C17 contain data, and then tests to see if it is less than or equal to 3.
If the formula resolves to TRUE, the entry is allowed, and if it resolves to FALSE the entry is disallowed and the error alert is displayed.
Step 5: Optional – Add an input message to give instructions as to what is expected.
Step 6: Optional – Add an Error Alert in case they try to buck the system!
Tip: there is a regular Validation List is in cell C5 for the child to self-asses their behaviour for the year (Santa takes this with a grain of salt ;-)):
Next year Santa will be stepping it up and collating the present requests via an online Excel Survey so he can leverage the use of PivotTables and other Excel tools.
Other Uses for Custom Data Validation
Another use for Custom Data Validation is to set a monetary limit for a range of cells. Let’s say you’re collating budget data and each department is allowed to budget for 3 projects but the total must not exceed $50,000.
The Data Validation formula is:
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
KAJAL says
I NEED A HELP. I WANT TO ENTER S IN A ROW . I WANT TO SET ITS LIMIT. FR EX. I WNT TO ENTER S 4 TIME . IF I TRY TO ENTER S 5TH TIME .IT SHOULD GIVE ERROR. IS IT POSSIBLE.
Mynda Treacy says
Hi Kajal,
You could always put data validation in the 5th cell to prevent anything being entered. Might be easiest if you post your question on our Excel Forum with a sample Excel file and I can give you an example of what I mean.
Mynda
Jon Acampora says
Great tutorial Santa (I mean Mynda)! That Santa hat is awesome btw!
Mynda Treacy says
🙂 thanks, Jon.
I made that picture in Excel. Who needs Photoshop!
Col Delane says
Further to Sumit’s query and suggestion, the custom DV formula could be something like this:
= AND( COUNTA($C$8:$C8) = ROWS($C$8:$C8), COUNTA($C$8:$C$17) <=3)
Note the lack of a $ before the 2nd cell ref in each of the ranges in the 1st leg of the AND function, which allows the condition check range to expand with the input range.
With this DV formula, users must enter their requests in order starting from row 8, but can only enter up to 3 requests in total.
Remember that any DV formula must return TRUE (i.e. no number or text string, etc. – just TRUE) for the input to be accepted into the cell, so you just need to put on your algebra hat and keep tinkering until the formula logic produces that result.
Also note the spaces between different parts of the formula, which do not adversely impact its working, but allow the user to more easily decipher it.
Mynda Treacy says
Thanks, Col. Great tip 🙂
Oz says
This is such the awesome blogpost!
Good for Santa!
Mynda Treacy says
🙂 Thanks, Oz.
Jef says
Useful and practical information. Works a treat. Thanks for sharing.
Mynda Treacy says
Thanks, Jef 🙂
Sumit Bansal says
Hello Mynda.. Great Tutorial. Very Creative.
One question – The current validation allows entry in C11, if any of the cell in C8:C10 are empty. Would it be better if data entry is allowed only when there is a number in the corresponding cell in Column B.
I tried it with this formula: =AND(ISNUMBER(B8),COUNTA($C$8:$C$17)<=3)
Thanks for the awesome article!!
Mynda Treacy says
Great idea, Sumit. Santa says ‘thanks’ 🙂