A drop-down list, like one provided by Data Validation, is a convenient way to input data by providing the user with a predefined list. A more sophisticated use of a drop-down is to have two drop-downs, where the first drop-down’s selected item dictates the items presented in the second drop-down.
How Dependent Drop-downs Work (let’s go to the movies)
Suppose we have a list of movies categorized by genre. The list may appear as follows:
Imagine a drop-down that lists the movie genres.
If we were to select “Comedy” from the first drop-down we will see a list of comedy movie titles in the second drop-down.
If we select “Sci-Fi” from the first drop-down, the list of movies in the second drop-down changes to only display science fiction movies.
The Problem with the Second Drop-down
If the user selects a movie from the second drop-down but then changes the selection in the first drop-down, the second drop-down does not change. This leads to confusion because the genre and the movie no longer match.
Some may argue that the above example is correct. 🙂
This behavior troubles many Excel users. We can solve this problem with a VBA solution, but for those users who do not wish to entertain a VBA solution we can solve the issue without VBA, we just need to be creative.
Featured Course
Black Belt Excel Package
Using a Form Control to Solve the Problem
By using the Combo Box Form Control, we will be able to achieve the desired behavior from our dependent drop-down list.
A Combo Box Form Control has many advantages over a traditional Data Validation drop-down list.
- The drop-down indicator is always visible in a Combo Box; where a user must click on a cell to reveal the Data Validation drop-down indicator
- Dependent Combo Boxes will automatically react to subsequent changes made in the first Combo Box.
Implementing the Combo Box Solution
Examine the data set below contained in cells A3 through C18:
What we want is for the user to select a Division from the first Combo Box and then be presented with a list of associated Apps in the second Combo Box.
To create our first Combo Box, select Developer -> Controls (group) -> Insert -> Combo Box.
SIDE NOTE: If the Developer tab is not displayed in Excel, right-click on any ribbon button and select “Customize the Ribbon”.
Check the option for Developer and select OK.
The Developer tab will be available.
After selecting Combo Box from the Form Controls list, drag a rectangular box on the grid. The exact size and placement are not important; we will adjust the appearance later.
To setup the Combo Box for use, we need to right-click on the Combo Box and select Format Control.
The two important properties of the Combo Box are:
- Input Range – The cells that possess the items to be displayed in the drop-down list.
- Cell Link – The cell used to hold the user’s selection from the list. Because a Combo Box sits above the cells, we need to store the user’s selection in a traditional worksheet cell.
For this example, we will input the following values:
Observe the behaviors of this configuration:
When we select the drop-down, we only see the first item in the selected range. This is because Combo Boxes do not understand horizontal lists.
Using what we have, if we select “Productivity Div” from the drop-down, we see the number “1” stored in the cell link location (K4).
The displayed text is not stored in the cell, rather the selected item’s position in the list is stored in the cell. Since “Productivity Div” is the first item in the list, we store the number “1”.
To overcome the problem of working with horizontal lists, we will create a vertical version of the list in our data preparation table.
Select the three destination cells (L4:L6) BEFORE you write the below formula. By highlighting the cells prior to writing the formula, we are telling Excel to place all the answers in an array of cells as opposed to placing all the answers in a single cell.
Using the TRANSPOSE function, we will write the below formula:
=TRANSPOSE(A3:C3)
Make sure you press CTRL-Shift-Enter instead of a traditional Enter. This will enter the formula as an array function. You can tell this is a special formula because Excel has added the curly brackets around the formula.
{=TRANSPOSE(A3:C3)}Now we will return to the Combo Box and adjust its properties to point to the transposed list ($L$4:$L$6).
Notice when we make selections from the Combo Box, the cell link number changes to reflect the selected item’s position in the list (1=”Productivity Div”, 2=”Game Div”, 3=”Utility Div”). Remember, we are recording the position in the list of the selected item, not the item text.
Creating the Dependent Combo Box
Return to the Developer tab and insert a second Combo Box below the first Combo Box. Note: you can also copy-paste Combo Boxes if you wish to retain the same size and characteristics of the first Combo Box.
In order to populate the second Combo Box with Apps, we can use various approaches:
- Directly reference cells containing the items.
- Create a dynamic list using a formula. Because the Combo Box will not accept a formula as a reference, we would have to store the formula in the Name Manager and then reference the name in the Combo Box.
Since we are using a data preparation table, we will use the first approach, direct cell referencing, to populate our second Combo Box.
We want to create a dynamic list of Apps based on the selection made by the user from the first Combo Box. We will use the value stored in the first Combo Box’s cell link (K4).
We will create the list of associated Apps using the INDEX function. In cell N4, enter the following formula:
=INDEX(A4:C4,,$K$4)
We are searching through cells A4 through C4 for an item to return. The number stored in cell K4 gives us the position to return.
We will leave the cell references for A4 through C4 as relative references. This way, when we drag down to create the remainder of the list, we will examine a different set of cells for each item in the list.
We need to set the K4 reference to an absolute reference ($K$4) so it will remain the same when we drag the formula down the remaining cells.
Reprogramming the Combo Box
Return to the second Combo Box and update its properties to read as follows.
We are setting the Input Range to point to the list of generated Apps (N4:N18) and the Cell Range will be cell M4. This will store a number indicating the position of the item selected by the user.
Testing the Combo Boxes
If we select the second Combo Box, we will see a list of Apps associated with the selected division from the first Combo Box.
The best part occurs when we change the selection in the first Combo Box, the second Combo Box automatically changes to reflect a selected App from the App list.
Using the Selections in the Real World
Using the list of Apps and their associated revenue (see below), we wish to return the revenue for a selected App.
We will use a VLOOKUP formula to search the list for the selected App and bring back the revenue.
The only information we have that indicates the user’s App selection is the position number stored in the link cell (M4).
We can’t use that number in a VLOOKUP because we are looking for an App name, not a number. We will use an INDEX function to convert that number into an App name.
INDEX(N4:N18,M4)This will extract the name from column N based on the position defined in M4. This will serve as the “item to be found” in the VLOOKUP function.
Create the below formula in cell G7.
=VLOOKUP(INDEX(N4:N18,M4),A32:B71,2,False)
The VLOOKUP reads as follows:
- Find the item defined by INDEX
- Locate the item in the first (left-most) column of values located in array A32:B71
- Return the item from the 2nd column of the discovered row
- Use the “False” option to use Exact Match mode when searching for the App name (this is necessary because the App names are not in sorted order)
The Problem with “Short” Lists
Notice when we select “Productivity Div”, the App list Combo Box has no empty lines at the bottom. When we select “Game Div” or “Utility Div”, we have some empty slots at the bottom of the list because the referenced cell range is not filled completely.
We can solve this small issue by creating a more advanced formula that will discover the length of the current App list.
We can’t place formulas in form controls, so we will place the formula in the Name Manager and then reference the name in the form control.
A useful approach is to construct the formula in a normal worksheet cell; once it is determined that the formula is performing as desired, we will transfer the formula into the Name Manager. One of the prime benefits of this approach is the support for IntelliSense assistance.
We will use an INDEX function to determine the length of the dynamic App list.
Knowing that the list will always begin in cell N4, we will start the formula as follows (place this formula in any empty cell):
=$N$4:INDEX(The resultant array will always begin in cell N4 but will end in different placed depending on the selected Division.
The interesting thing about this approach is that when you use an INDEX function as part of a range definition, the INDEX function returns a cell address as opposed to the contents of the discovered cell.
The array that INDEX will use is the range of possible answers that may be returned. In this case, cells N4 through N18.
=$N$4:INDEX($N$4:$N$18The next argument is to determine how many rows to capture in this array. We want to capture as many rows as contain data. This will be determined by the MATCH function.
Since we aren’t looking for an exact match of anything, we will look for the highest possible App name that could ever occur in our list of Apps.
We will use a static “fake App” name of “zzzzzzzz”. If this were to exist and we sorted our App list, this App would always be at the bottom of the list.
=$N$4:INDEX($N$4:$N$18,MATCH(“zzzzzzzz”We will look for this “fake App” in the list of dynamically generated Apps (N4:N18).
=$N$4:INDEX($N$4:$N$18,MATCH(“zzzzzzzz”,$N$4:$N$18We want to return the location of the last item discovered, so we will use a “1” to denote a “less than” behavior.
=$N$4:INDEX($N$4:$N$18,MATCH(“zzzzzzzz”,$N$4:$N$18,1))When the MATCH returns the location of the last discovered item in the array, INDEX will turn that last discovered location into a cell address and append it to the constructed range.
=$N$4:INDEX($N$4:$N$18,MATCH(“zzzzzzzz”,$N$4:$N$18,1))becomes
=$N$4:INDEX($N$4:$N$18,12)which becomes
=$N$4:”Arcade”which becomes
=$N$4:$N$15Transferring the Logic to Name Manager
Now that our formula is working properly, we will transfer it to a Named Range in the Name Manager.
- Highlight the formula and copy it to the Clipboard.
- Select Formulas -> Defined Names (group) -> Name Manager -> New.
- In the New Name dialog box, give the named range a unique name, such as “Combo” and paste the formula in the Refers to:
- Return to the properties of the second Combo Box and update its properties with the following information; Input Range = “combo”.
The Combo Box now refers to the INDEX/MATCH function stored in the Name Manager.
Observe that the second Combo Box no longer displays empty slots for shorter App lists.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
