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.
Enter your email address below to download the sample workbook.
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:
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 ;-)):
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: