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:
=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:
I want to validate data for exam marks entry.
Marks are in whole numbers. No decimal number allowed. Also we will enter “AA” for absent students and “ML” for students on medical leave.
Kindly suggest me.
Thanks.
Hi Arvind, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Hi, Love your content, thank you for sharing.
With customize validation, how could I limit the number of times a specific election was used. I want to post a spreadsheet for training sign up, so a training session on Sat 15th xxx has 15 seats available, and hence should only be selected 15 times. Once we have 15, it is no longer selectable. if someone changes their mind and deletes the 15th, that now allows someone else to select the 15th.
Thanks in advance
Hi Steve, perhaps this reducing data validation list technique will help.
Mynda
Hi, Thanks for your information. I created a questionnaire with six cells on the right of each question. The first cell is for “disagree strongly” and is supposed to be filled with a “1” the second cell is “disagree somewhat” and is supposed to be filled with a “2” etc. I want to restrict the user to entering an answer into only one cell and to restrict the user to entering a 1 in the first cell or a 2 in the second etc.
Please advise.
Thanks!
Hi Patiel,
I would use Option Buttons inside a Group Box as described in this post on Form Controls.
Mynda
Hi,
can you help me with a solution to get of formula for the “Custom” data validation function?
I would like to fill a cell with a “positive number” >0 and only one text entry “UNK”.
thanks for willing to help me.
Cor
Hi Cor,
Can you please post your question on our Excel forum and provide a sample Excel file with some examples of what is acceptable in the cell. It sounds like you want a number and text in the same cell e.g. 9UNK where 9 needs to be > 0, if so, I don’t think this is possible.
Mynda
=OR(A1>0,A1=”UNK”)
Hi, I have a very basic spreadsheet set up which is working out the productivity of a small workshop, in cells D2 and D3 they enter the working days of the week, D2 can only enter 1-4 and D3 can only enter 1, this then multiples these values by 8.5 and 7 respectively to give a value of 41 hrs, then they add hours to the days which throws up a value in another cell based on =S8/((T2)+(T3))*100, S8 being the productivity value, T2 = the value of cells D2 and T3 the value of D3 (Mon to Thurs 4×8.5 and Fri 1×7), if all the fields are entered to the max ie Mon to Fri 8.5hrs and Fri 7 hrs it returns a value of 100, which is correct, but I need to error S8 if the user makes an error in inputting ie 3 days instead 4 days Mon to Fri, or or Fri that drops the calculation to below 41 hours, as the Productivity calculation goes above 100%, which is impossible.
Hi John,
It’s difficult to answer this without seeing your file. Can you please post your question on our Excel forum where you can upload a sample file and we can help you further?
Mynda
Hi, thank you very much for the wonderful Santa tip. I have a question on how to apply this wholesale to multiple columns, but each column with its own count. I.e. 10 columns side by side and then another 10 columns side by side in a different area of the worksheet, like that I have probably 50 areas; but each column should have its own formula. Is doing it one by one the only way?
Thanks,
Krishna.
Hi Krishna,
I don’t know of a way you can copy the data validation rules and have it automatically refer to a different range of cells. AFAIK the references need to be absolute.
Mynda
Thanks for the reply.
K
Hi, Need Help. I have 10 cases and 100 different entries are mapped against these 10.It Means 10 cases against single common case. Now I want to validate this data where after selecting any one case out of 10 cases, mapped cases (10 cases) should show in next validated cell.
pls let me know if this is possible.
Hi Rohan,
It sounds like you need dependent data validation lists. Here are some different approaches:
https://www.myonlinetraininghub.com/excel-dependent-data-validation
https://www.myonlinetraininghub.com/excel-data-validation-with-dependent-lists
If you get stuck please post your question in our Excel forum.
Mynda
Thank You for Your Help!!!
HI, I need help regarding a problem. In my scenario I have a list from A1:A10 and require to set a custom validation in A11 in such a manner so that whatsoever will be the value in cells A1:A10. It can not be entered in cell A11.
Requirement :- The data in A1:A10 is being updated on daily basis but the A11 cell should not be entered with a value which is already there in the list from A1:A10. Value can be anything i.e. be it Text, Decimal, Special character, etc.
Thanks in advance,
Nikhil Verma
Hi Nikhil,
Add a custom validation to cell A11 (in Allow: field, select the last value- Custom), with this formula: =NOT(ISNUMBER(MATCH($A$11,$A$1:$A$10,0)))
Catalin
Hi, I needed some help. I am trying to put custom data validation to a cell where there are a case of circular reference.
I want the value in G6 to be less than or equal to the value in H3. But the formula for H3 is SUM(C3:C34) – SUM(G3:G34) and hence there is a circular reference since the value of G6 itself affects the value of H3. What I want is that I want to limit the value of G6 the value of H3 before the value of G6 to be put in.
Suppose, the cell G6 is blank and the value of H3 is 5. I want the value of G6 to be less than or equal to 5. But if I put any value in G6, it changes the value of H3 since there is a circular reference. I want the formula to hold for the value of H3 prior to putting the value of G6.
If I make change in the value of any cell before G6 that affects the value of H3, that should change the formula but any cells after G6 that affect the value of H3 should not change the formula. Is all this possible?
Hi Prateek,
Conditional iterations are not possible in excel, iterative calculation can only be enabled or disabled. I’m afraid you have to build another scenario to get your desired outcome.
Catalin
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.
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
Great tutorial Santa (I mean Mynda)! That Santa hat is awesome btw!
🙂 thanks, Jon.
I made that picture in Excel. Who needs Photoshop!
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.
Thanks, Col. Great tip 🙂
This is such the awesome blogpost!
Good for Santa!
🙂 Thanks, Oz.
Useful and practical information. Works a treat. Thanks for sharing.
Thanks, Jef 🙂
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!!
Great idea, Sumit. Santa says ‘thanks’ 🙂