Adding a drop-down to a cell in Excel (also known as validation) is fairly straight-forward. But adding interdependent drop-downs is slightly more difficult. In this post we review several methods to create multiple dependent validation lists in Excel. We will start with a classic and simple way to do it that does not require any VBA macros. However, the classic way to create multiple dependent validations is a tedious process and may take some time in order to create the necessary lists, tables, names, etc. Therefore, we will also look into an alternative to create the dependent validation using VBA macros. In this case, the macro could be adapted to accommodate as many fields as needed, thus being ideal for multiple dependent validation. As an example, we will create a validation list to select countries, regions, and cities of the world.
Create Standard Dependent Validation (no macro needed)
First, we need a unique list of items to list in the first field. In this example, that’s a list of four European countries. We add a validation list to any cell from Data -> Data Validation, selecting the list option under “Allow” and the corresponding range below under “Source”. We can directly select the range with the list of countries, but is often more practical to define a name for that range, and then simply refer to that name in the validation list. Furthermore, when put in a table, the name is dynamic and will update if new items are added to the table. The image below shows how to define a name (Country) with the list of countries.
Now we can add a validation list to select the country in cell B3. We just need to refer to that name as the source of data for the validation list. Note that we need to add the equal sign before the name.
Another way to do it is referring directly to the table name (no defined name needed). In the example above, the table has the default name (Table1). We could change that to a representative name if appropriate. Then we could specify the source of the validation list as =INDIRECT(“Table1[Country]”), where Country is the header for that column (it happens to have the same name as the ranged we defined earlier).
We can add another validation (field 2) to select a region for a given country. Thus, the values are dependent on the selected country in the first field. For that, we need to create another table with as many column headers as values (countries) in the first list. Then we add the corresponding regions for each country underneath and define a named range for each of them.
Now we add the dependent validation to any other cell (cell C3 in this example), referring to the first field with =INDIRECT(B3) as source.
We can yet add another validation (field 3) to select the city for a given region, just repeating the previous step again. We need to create tables for each country with as many column headers as regions, with all the cities underneath. Then we define a name for each range and add the validation list in cell D3 using =INDIRECT(C3) as source.
That simple example includes just a few countries, regions, and cities. But, what if we want to have a validation with all the countries in the world, with respective regions, and most or major cities? The classic approach requires enormous effort, too many tables, names, etc, and using macros instead makes the job easier.
Create Dependent Validation with VBA Macros
We could automate the process explained earlier with macros. However, that would still require the creation of a considerable number of tables and defined names in order to add the validation lists. With this amount of data, it is better to have a macro update the fields every time a new country is selected, and then use filtering options to copy the regions and cities from the main data source. The data is arranged in three columns: countries, regions, and cities. There are more than 23 thousand cities (and rows) altogether. See the worksheet Data in the Excel file available for download below.
The first macro creates a unique list of countries from column A, puts that into a separate sheet (List), and then adds the first field of validation to cell B5 in the main worksheet (Validation). This macro just needs to run once.
The second macro adds the validation list for the second field (regions). First it clears the contents in other cells (regions and cities), and gets the selected country in B5 into a variable. Then it filters the main data table for that country (column A), and starts a loop through the visible cells in column B (regions). The macro puts each entry into a collection to get only unique entries, and then adds each unique entry to column B in worksheet List. Finally, the macro adds the validation list to cell D5 with the list of regions generated in the previous step as source.
This macro must run when a country is selected. Thus, we need an event procedure to run when the value in cell B5 changes. That must be added to the Validation sheet module. Note that it also includes the change to D5 in order to update the validation list for cities too (see next macro).
We can add as many validation fields as columns in the dataset. In this example, we want to add a third validation to select the city for a given region of a given country. We just need to copy the previous macro and slightly change the code to filter the second column in the dataset, get the list of cities in the third column, and add the validation to cell F5 in the main worksheet. As this is the final list of items (cities) and they are already unique, we do not need to put that into a collection and can directly generate the list from the visible items in column C after filtering.
We have now the multiple dependent validation for all countries and regions, and most cities of the world.
Download Excel Multiple Dependent Validation
No comments:
Post a Comment