Prevent Cheating With an Excel Drop Down List

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.

DVDep01

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!

DVDep02

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

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)

DVDep03

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.

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

_________________

You may also like...

2 Responses

  1. Pete says:

    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!

  2. Pete, thanks for letting me know that you like the site. I’ve added a picture to the article, to show an example of highlighting the inconsistencies.

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *