Excel is a powerful tool and has a lot of opportunities that people aren’t aware of. In this post, I’m going to talk about one of these, the ability to easily make a dynamic sub-menu.
The Example
We are going to make a simple look up, which when you specify a country will give you a list of major cities. For this example, we are only going to use five countries and ten cities in each. If we were doing this for all countries and all cities we might choose to use a different method.
Creating the data for the dynamic sub-menu
The first thing we are going to do is create six tables, one table for the names of the countries and one table for each of the countries. In my case, I have chosen Belgium, Croatia, Italy, Spain and the UK, and Googled a list of ten cities in each of them.
You’ll notice for I have made the column header for each table to be a description of what the table contains. This is just for clarify. Depending on how clear you like your references to be, you could also go ahead and specify a name for each of the tables by going into Table Tools > Design, then updating the Table Name. It is important that you don’t name any of the tables just their country name, you’ll see why later. Instead, I would suggest something like UKCities.
Creating the named ranges
When we are creating the drop down lists we will need to use named ranges to specify the information that should be displayed. You cannot specify a table as the Data Validation source, therefore we need to use a named range.
To define a new named range we can go to the Formulas tab and click on the Define Name button.
This will then give us a pop-up where we specify the name of the named range and what cells it refers to. You will need to type in the table name and column as selecting the cells will give you absolute references.
We now have our countries named range created and can create the first of the two drop downs. This can easily be achieved by selecting the cell you wish to add the drop down to and selecting the Data Validation button in the Data tab.
Once you’ve opened up the Data Validation box, you’ll need to change the Allow type to List. This will then allow you to specify the source for the drop down list, in our case, it’s going to be the Countries named range.
Once you’ve done that you will have the following:
Now for the dynamic sub-menu part of this, for this, we are going to use the INDIRECT function. What the INDIRECT function does is allow you to specify a cell or range through text. In our case, we are going to take each of the country names and turn them into a named range for their cities.
To achieve this we just need to follow the steps above, creating a named range called UK for the table of UK Cities.
Once we have done that for all of the sub-lists we can then create the drop down list for the cities. For clarity, I have changed the name of the country cell to SELECTED_COUNRTY, though you don’t need to do this. We then add data validation to the Cities cell using the formula
=INDIRECT(SELECTED_COUNTRY)
Which will look like this.
When you click on OK, you might get a warning that this formula gives an error, that is fine and to be expected. What is causing this is that there currently isn’t a country selected. Your users won’t be shown this.
We are now complete, if you select one of the countries then the options available in the city cell will be those specified in the corresponding table. If you add an item to the table it will automatically be added without having to worry about changing the named range.
Why I chose to use Tables
It is possible for you to create named ranges within using tables but I have chosen to use them for a few reasons:
- You can add new items without worrying about the size of the named range;
- You can easily sort them; and,
- Personal preference but I like that they have clearly defined areas, where all content within them is related to the one subject.
