In Monday's blog, you saw how to make simple dependent data validation drop down lists. After creating the drop downs, you added some flexibility by using the IF function in the data validation formula.
Today you'll see a couple of problems that can occur when you refer to other cells in your data validation, and those cells are blank.
Invalid Entries Are Allowed
If you create a data validation formula that refers to another cell, and that cell is empty, users might be able to type invalid entries in the cell. For example, in the screen shot below, Buffalo was entered in cell B4. As you can see in the list in cell B5, that isn't one of the cities allowed when the adjacent cell in column A is empty.
Change the Ignore Blank Setting
To prevent people from entering invalid data when the cell referred to is empty, you can open the Data Validation dialog box, and remove the check mark from the Ignore Blank setting.
With the Ignore Blank setting turned off, users will see an error message if they try to enter invalid data.
Problem with Circle Invalid Data
I always turn off the Ignore Blank setting when using dependent data validation drop down lists, as I described above. However, last week I heard from Paul, who uses the Circle Invalid Data feature in one of his workbooks.
When the Ignore Blank setting is turned off, Excel treats empty cells as invalid data, when you run the Circle Invalid Data feature. That's a helpful feature when you don't want to allow empty cells, but not very helpful in this case.
To remove the circles, use the Clear Validation Circles command on the Excel Ribbon's Data tab.
Make a Choice
Despite extensive experiments, I couldn't find a formula that would prevent invalid entries in a dependent data validation cell, where the referenced cell is empty, without turning off the Ignore Blank setting. If you find one, please let me know, and I'll update this blog post.
In the meantime, I'd rather prevent invalid entries, than catch them later, so I'll stick with that setting change. I don't really use the Circle Invalid Data feature anyway, so I won't miss it!
Watch the Video
To see the steps for turning off the Ignore Blank setting, and the problems that can occur, please watch this Ignore Blank Problems in Excel Data Validation video.
_______________
One way you can avoid the circles problem is by prefilling all the data validation cells with default values. It doesn't work for all layouts, but whenever possible, I like to put a default value in the cell. The user isn't filling a blank cell, rather they're selecting a different entry from the list. It also introduces the problem of the user forgetting to change it and having valid, but ultimately incorrect, data in the cell.