Prevent Excel Dependent Drop Down List Problems

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.

Excel Dependent Drop Down Lists
Excel Dependent Drop Down Lists

Dependent Drop-Down List Problem

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!

Changing Category Selection
Changing Category Selection

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.

DVProdDialog

Change Data Validation Formula

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(C2=””,Produce,B2)

Change Data Validation Formula
Change Data Validation Formula

Cannot Change Category

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.

DVDep04

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:

=COUNTIF(INDIRECT(B2),C2)=0

CF_Countif01

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.

Highlighted Items

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.

CondFormatCountIf
_________________

0 thoughts on “Prevent Excel Dependent Drop Down List Problems”

  1. i like your site, it’s very helpful. thank you for sharing your expertise. can you please show the expected effect of duing the “highlight inconsistencies”? thanks & more power to you!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.