This is a treat that you probably haven’t tried before, and it could prevent your worksheet’s data from turning into a nightmare!
To show how this trick works, I used the movies from the Horror section in The Guardian’s Greatest Films spreadsheet. The same movies were used a couple of Halloweens ago, to show how Slicers work. About half of the horror movies were made in the USA, so to keep things simple, I grouped the countries as USA and Other.
Let the Right One In
To show how this trick works, I set up list of countries (USA and Other), then a list of the top horror films made in each of those countries.
The data in each list is named:
On the Data Entry sheet, I added a data validation drop down in column B, based on the ListCountry range.
In column C, I created another drop down, and it uses the INDIRECT function to create a range reference:
=INDIRECT(“List” & B2)
- USA is selected in cell B2, so the drop down in C2 shows the items in the range named ListUSA.
- Other is selected in cell B3, so the drop down in C3 shows the items in the range named ListOther.
Don’t Look Now
Things can go wrong though, if someone goes back to the Country cell, and changes it. Then, the country and movie selections are mismatched -- for example, Country is changed to Other, and Texas Chainsaw Massacre is selected in the second column. Oh, the horror!
Fortunately, we can prevent this kind of evil activity, by changing the data validation formula in the Country column.
Currently, the formula just refers to the country list: =ListCountry
I’ll change the formula, so it checks to see if the movie cell is empty. If the cell is empty, it will show the ListCountry items.
If the movie cell is not empty, it will try to show the items in the range named “FakeRange”. I can’t use that name on its own in the formula, because Excel will detect that the range doesn’t exist. So, I’ll use it with INDIRECT, and Excel will let me enter the formula. If C2 has an entry, Excel will warn me about the error, but I can click “Yes” in the message, to ignore it.
There isn’t any range with that name in this workbook, so the result is an error, and the drop down list doesn’t work. The arrow appears, but nothing happens when you click on it.
Ha! We are truly Les Diaboliques. But we are doing this to protect The Innocents, who might accidentally mess up our worksheets in the Dead of Night.
Video: Block Changes in Excel Drop Down List
Watch this short video to see how this technique works, and see how to set up the data validation in both columns.
Or watch on YouTube: Block Changes in Excel Drop Down List
Download the Sample File
To download the sample file, please visit the Dependent Data Validation page on my Contextures website. The file has several sheets, with a variety of conditional drop down examples.