You know how to create a drop down list in Excel, by using the Data Validation feature. Sometimes you get extra fancy, and make a drop down list that depends on the selection made in another list, using dependent data validation.
Dependent data validation works well, but there’s a loophole. In the screen shot below, I can change the category to Fruit, even though a vegetable, Cabbage, is selected as the Item in that row. With this loophole, you could end up with some strange data on your worksheet!
Lock the First List
Excel users are very resourceful, and can find ways around almost every safeguard that you set up. However, you can slow them down a bit, by making the first list dependent on the second. They’ll have to clear the Item selection before they can change the Category.
Currently, the Category cells have a data validation list that’s based on the named range, Produce.
You can change the formula in the Source box, so it only uses the Produce range if the Item cell is empty. In the screen shot below, cell B2 is active, and the Data Validation Source formula is:
If cell C2 is empty, the Produce list will show in the drop down list. If an Item was selected in cell C2, then the drop down list will show contents of cell B2.
Highlight Any Inconsistencies
It’s still not foolproof. Users could clear the Category cell, then enter any value. Or they could cut and paste from another cell. As an extra precaution, you could add conditional formatting in column C, to turn the cells yellow if the selected Item is not in the selected Category.
With cell C2 active, the conditional formatting formula is:
The bright yellow colour will alert users if there’s an inconsistency in the selections, and make it easier for you to spot any problems.
For example, in the following screen shot, Cabbage and Rutabaga are highlighted in yellow, because the Category has been changed to Fruit. Those items don’t appear in the range named Fruit, so the COUNTIF formula returns a zero.