Excel Advanced Data Validation Technique
Does your drop down list depends on the selection from another drop down list? Below we will discuss an Excel Advanced Data Validation Technique showing how to create a drop down list which depends on the selected item from another drop down list.
I discuss two approaches. The first of where we stack the lists in one column and the other where we follow a more complicated path. I will explain in both cases what needs to be considered.
We are developing a range of bicycles where each bicycle is manufactured at one of various factories located in three countries namely Taiwan, China, and India.
Method 1: Using Offset and Match to find the factories from a selected country
We create a source list where we list all the factories in one column (B2:B10). In the preceding column we list the country in which this factory is located (A2:A10).
I add a list to the Name Manager with the countries names. To do this, type Crtl-F3, or open the Name Manager from the Defined Names Group on the Formulas Tab. Select New and type in a name for the Countries-list – the counties are listed in I2:I4. Note that this name are not allowed to have any spaces. I named the list CountryOfOrigin (or alternatively words can be separated by underscores such as Country_Of_Origin). Click in the Refers to box and drag your mouse over the range containing the names of the countries (I2:I4) to define the list as =Sheet1!$I2:$I4.
In the Results-Table where I want the dropdown lists to be available (A15:B23), I select the range in Column A and click on Data Validation in the Data Tools group on the Data tab. Then select Data Validation… in the dropdown menu.
In Settings, in the Allow selection box, select List. Then click in the Source box and press F3 to open the Paste Name selection box and select the list name CountryOfOrigin. Ok, we can now select the countries from the dropdown list in Column A.
The trick is now to create dropdown lists in Column B which only list the factories available for a corresponding country.
I do it as follows. Select the range in column B where I want the dropdown lists to appear (B15:B23). Now open the Data Validation dialogue box again as described earlier. Note that this time I will enter a formula. It is important to notice that even though I will be using a formula, I will still select List (and not Custom). I enter the formula in the Source box on the Settings Tab. The formula I am entering here looks as follows:
The MATCH function will match the selected country to the source-list.
The COUNTIF function will count the number of factories for the corresponding country. Note that the source-list must be ordered because this determines the height in the OFFSET function. For example, the height of the list of factories in Taiwan is 3 (rows), while the width is 1 (column). Effectively the formula above really defines a list for Cell B15 of =OFFSET ($B$1, 1, 0, 3, 1).
Method 2: Create a dynamic list for each of the countries
In this method, I can easily add factories as needed without the need to sort my list according to country as in the previous method.
Like in Method 1 I also create a named list for the countries. But in Column J I identify the column number where the list of factories appear for the corresponding country. The formula in J2 is:
In Column K I define the column with
=SUBSTITUTE(ADDRESS(1,J2,4),"1","") & ":" & SUBSTITUTE(ADDRESS(1,J2,4),"1","")
Column L counts the number of factories as specified in Column K
Column M creates the dynamic list reference for factories in each country
="'Sheet1'!" & ADDRESS(2,J2,4) & ":" & SUBSTITUTE (ADDRESS(2,J2,4),"2",L2)
With these List references I created a name list for each country in the Name Manager, using the INDIRECT function. That is, the Name List TaiwanFactories Refers to: =INDIRECT(Sys!$M$4)
Selecting the range where I want the dropdown lists to be available, I use Data Validation, Allow List, and enter the formula in the Source box on the Settings Tab as follows
=INDIRECT(OFFSET(INDIRECT("Sheet1!I" & MATCH(A15, Sheet1!I:I,0)),0,4))
The MATCH function matches the corresponding country to the list of countries in Column I and returns the relative row reference.
MATCH(A15, Sheet1!I:I,0) will return the value 2 since Taiwan is listed in Row 2 of Column I.
The INDIRECT function returns an actual cell reference. That is
INDIRECT("Sheet1!I" & MATCH(A15, Sheet1!I:I,0)) is the same as
INDIRECT("Sheet1!I2”) and this translates to =Sheet1!I2
Then OFFSET(INDIRECT("Sheet1!I" & MATCH(A15, Sheet1!I:I,0)),0,4) is then the same as OFFSET(Sheet1!I2,0,4) which returns the contents of Cell M2, in this case, 'Sheet1'!R2:R4 and thus will INDIRECT('Sheet1'!R2:R4) define the list of factories in Taiwan in the Name List.