In this tutorial we are going to look at an actual use case involving checking or unchecking the "Ignore blanks" checkbox in Data Validation in Excel.
You can download the file here and follow along. When you get a preview, look for Download in the upper right hand corner.
Here I have an area to select up to 5 courses and indicate Yes, No, or Maybe regarding that choice:
I have two tables, Table1, which has the list of courses to choose from:
and Table2, which has two columns that will be used for the Yes/No column of my course selection area:
The Data Validation for the course selection is:
and the Data Validation for the Yes/No column is:
The formula that we used for the Yes/No column is:
=IF[LEN[C2]>0,INDIRECT["Table2[YNM]"],INDIRECT["Table2[NoChoice]"]]
What this says is IF the length of the cell with the course is greater than zero [meaning a choice has been made] use the YNM column of Table2, otherwise, use the NoChoice column, which are just blanks. So if I choose a course, then I can decide to indicate Yes, No, or Maybe regarding that course.
So, if no choice is made, then the drop down list is a series of blanks:
But if a choice is made, then the drop down lists gives me some options:
The issue comes in when the course choice is blank, Excel allows anything to be typed into the Yes/No column, even though the Error Alert is set at Stop:
The reason is because the "Ignore blanks" option is checked for the Yes/No column:
The key here is that we do NOT want Excel to ignore the blanks. If we uncheck that box:
and try to type anything, even valid options like Yes or No, the Stop Alert will not allow us to proceed, and we can only enter blanks:
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, or
Check out my YouTube channel!
Happy Excelling!