Creating conditional dropdowns

Challenge: Create two dropdowns, the second one only showing values relevant to what has been selected in the first one.

This question has come up a lot in my Excel career. A good example is where you have two-tiered data. For example, you may want a user to select a continent and then select a country within that continent. Instead of presenting the user with the full list of countries across the globe, wouldn't it be handy to only show the countries in the selected continent? Here's how.

First, create an area in which the countries within each continent are listed. The title of each column should be the continent. Unfortunately, these column titles cannot contain spaces, so you'll have to use underscores if you need to separate words.

And immediately beneath each continent name, type the countries that make up that continent, using as many cells as necessary.

Now you'll need to create a few named ranges. Create one named range for each of the sets of countries (excluding the title). And lastly, create a named range called "Continents" made up of just the column headings.

In Excel 2007, this can be done throughout the Create From Selection option in the Defined Names area of the Formulas ribbon. But if your sub-options vary in number (as would be the case with countries within continents), it's best to create the ranges manually—otherwise you’ll have empty cells in some of the named ranges and these will carry over into the dropdowns, which isn't pretty.

So by now you should have lists of countries for Asia, Europe, North America etc., and named ranges for each set of countries (called Asia, Europe, North_America etc.). And you should have another named range called Continents containing a list of the values Asia, Europe, North America etc.

That’s the prep. work done. Now you need to create the data entry area. For this, you simply need two cells: one in which people will select the continent, and one in which they will select the country. Let’s assume these are A1 and B1 respectively.

In A1, hit Data Validation, select List and type "=Continents" (without the quotes) in the Source field. This will allow the user to select a continent from the list.

Now for the clever bit. Ready?

In cell B1, use Data Validation again, choose List and put "=INDIRECT(A1)" as the Source. The indirect function merely takes the value of A1 and uses it as text that can be in turn interpreted by Excel. Given that A1 can only contain the name of a continent and each continent has its own named range, the values allowed in B1 are defined by the named range associated with the continent selected in A1. And the two cells can be copied down the columns to make as many contextual dropdowns as you like.

• Tweet