Limit the choices in an Excel drop down list, depending on the value in another cell. For example, select 'Fruit' in cell B2, and 'apple, banana, peach' appear in the dependent drop down list in cell C2. Watch the video below, to see how it works, and written steps and sample files are below.
NOTE: There are other techniques for setting up dependent drop down lists, so choose the setup method that is best for your needs.
Demo: Dependent Drop Down Lists
This animated screen shot shows how a simple dependent drop down list works.
There are setup instructions and a video in the next section.
Video: Dependent Drop Down Lists
In this dependent data validation video, select Fruit or Vegetable in the first column, to limit what is shown in the drop down list in the next column. The written instructions are below the video, and the full transcript is on the Dependent Drop Down Lists Video page.
NOTE: There are other techniques for setting up dependent drop down lists, so choose the setup method that is best for your needs.
How to Make Dependent Drop Down Lists
Here are the step-by-step instructions for making dependent drop down lists. This example uses Fruit and Vegetable lists.
In the screen shot below, you can see the completed data entry sheet. Select a produce type (fruit or vegetable) in column B. Then, in column C, a dependent drop down list show items for the selected product type only.
There are a few steps for setting up the drop down lists, and the details are in the sections below.
Set Up the Worksheets
This example will have a workbook with two sheets -- a data entry sheet, and a sheet with lists.
Create Lists
Next, you'll create lists with items for the drop down lists.
In this example, you'll set up 3 lists. The main list has produce types, and the other two lists have items for the dependent drop downs.
To create each list, you'll do three things:
1. Type the Lists
To type the lists, go to the Lists sheet. Start with the main list - Produce types.
NOTE: The Produce list has one-word items in it -- Fruit and Vegetable. This is important, because those words will be used as Excel names, and two-word names are not allowed as names. If you need to use multiple-word items in the main list, see: Using Two Word Items
Add the Dependent Lists
Next, type the dependent lists, with the headings Fruit List (in cell D2) and Vegetable List (in cell F2). These lists can contain one-word items (apple), or multiple-word items (green beans).
2. Format Lists as Tables
Next, follow these steps, to format each list as an Excel Table. This makes your list dynamic -- the list size will adjust automatically if you add or remove items, so your drop down will show the entire list.
Follow these steps for each of the 3 lists:
Here is the Lists sheet, with all 3 lists formatted as Excel Tables
Create a Named Range
Next, follow these steps to create a named range for each formatted Excel Table. Later, you will use this name, when making the drop down lists on the Data Entry sheet.
Name the Produce List
Name the Fruit and Vegetable Lists
Next, follow these steps to name the fruit and vegetable lists.
Add the Main Drop Down
Next, you'll add the main drop down (Produce Type) on the DataEntry sheet. This is just a normal data validation drop down list.
Start the Data Entry Sheet
First, you'll enter headings on the data entry sheet, and set up a named table.
Add the Main Drop Down
Next, you'll the main drop down list, in the Produce Type column
Add the Dependent Drop Down
Next, you'll create a dependent drop down list in the Item column. This cell will have a formula to create the data validation drop down list. Read more about the INDIRECT function here.
Note: If cell B3 is empty, you'll see the message shown below. Click Yes to continue.
Test the Drop Down Lists
To test the dependent drop down lists, follow these steps
NOTE: If a Produce Type has not been selected, the Item drop down in that row will not work.
Dependent Drop Down - Cities
This is another example of dependent drop down lists in Excel. In this dependent data validation video, if a country is selected from the first drop down, only the cities from that country are in the drop down list.
This video shows the basic set up steps, and then shows how to use the Excel IF function with a dependent drop down.
Advanced Dependent Drop Downs
The instructions in the previous sections show how to set up a basic dependent drop down list. Here are a few advanced examples.
Use Two-Word Items
In some workbooks, you might need to have two-word items in the first Excel data validation drop-down list. For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow Fruit'
=INDIRECT(SUBSTITUTE(A2,' ','))
Main Items with Illegal Characters
You may need items in the first Excel data validation drop-down list that contain characters not allowed in range names, such as the ampersand (&).
For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow & Orange Fruit'. For the dependent lists, you can create ranges with one-word names, such as YOFruit. Then, create a lookup table, which lists each item in the first Excel data validation drop-down list, and the range where its dependent items will be stored.
To start, create the item lists and the first Excel data validation drop-down:
Next, you'll create the lookup table, to match each item with its dependent items' range name.
With Red Fruit selected in cell A2, the VLookup formula will return RedFruitList as the range name for the dependent list. The RedFruitList items will be displayed in cell B2's drop-down.
To create a 3rd dependent drop down list, use the same techniques, to create lookup tables and items lists.
In the screen shot below, there are lookup tables named RedFruitLookup, YOFruitLookup and GreenFruitLookup. Items lists, with the code names and 'List', have been added to the worksheet.
In the data validation window, use this formula for the 3rd drop down:
=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&'Lookup'),2,0)&'List')
The formula finds the lookup table based on the product type selected in cell A2, e.g. RedFruitLook, and gets the code for the selected product -- Mac. It adds 'List' to the code, and shows the items in the MacList range.
Add a Third Dependent Drop Down
If you need to, you can add another set of dependent data validation dropdown lists that depend on the selections in the first two dropdowns. select a country and region, then select a city in the selected country and region.
Note that the region names are not unique -- there is a West region in both Canada and the USA -- so we can't show a City list that is only based on the region names.
To make sure that the correct list of cities appears, the city lists will be named for the country and region. There are 2 countries, and each country has 3 regions, so we will set up 6 named ranges for the 3rd level drop down lists.
NOTE: If you need more than a few named ranges for your 3rd level lists, try the Dependent Lists with Tables technique instead -- it will be easier to set up and maintain.
To set up 3-level dependent lists:
Use Dynamic Lists
Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation won't work with lists that use formula-based dynamic named ranges, such as OFFSET ranges. Use one of the following solutions when a dynamic list is required:
Named Excel Tables
To avoid the problem, use named Excel tables, instead of formula-based dynamic ranges.
Then, use one of the INDIRECT function examples shown above, to create a dependent drop down list.
Dynamic Name Workaround
If you can't use the Named Excel Tables, use the following method for creating dependent lists from formula-based dynamic named ranges:
=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&'Col')),1)
if two-word items will be used, you can include the SUBSTITUTE function in the formula:
=OFFSET(INDIRECT(SUBSTITUTE($F2,' ',')),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2,' ',')&'Col')),1)
Prevent Invalid Selections
After someone selects an item from a dependent drop down, they could go back to the main drop down, and select a different item. That could result in mismatched items in that row.
For example, Fruit could be selected from the main drop down, and then Lemon in the dependent drop down. If Fruit is changed to Vegetable later, the Lemon selection would be incorrect -- it's not a vegetable.
To prevent invalid selections, here are a couple of techniques that you can use
Block Changes in First Drop Down
Free creation club mods. To block changes to the first list, you can change the data validation formula, so the list does not appear unless the second cell is empty. This video shows you how this technique works, and written instructions are below the video.
Block Changes in First Drop Down
With dependent drop down lists, problems can occur, if someone goes back to the first list, and changes it. Then, the first and second selections are mismatched -- for example, in the screen shot below, Vegetable is the first choice, and Banana is selected in the second column.
To block changes to the first list, you can change the data validation formula, so the list doesn't work unless the second cell is empty.
Instead of just referring to the Produce range for the Produce Type drop down, the formula will check for an entry in the Item column.
To change the formula:
Now, the drop down in the first column won't work if the Item has been selected in that row.
Clear Dependent Cell After Selecting
In the previous section, the data validation formula was designed to prevent selections from the first drop down, if the cell to the right contained data.
Another option is to use a macro, to clear the dependent cell, after making a selecting in the first drop down. That will prevent mismatched selections.
In this example, column B contains a drop down list of Regions. After you select a Region, the drop down list in column C shows the customers in the selected region.
Go back and select a different region, and the existing customer name will be cleared.
Add Event Code to Clear the Customer Cell
When you select a Region, event code clears the cell to the right. To add the code, right-click the sheet tab, and click View Code.
Paste the following code onto the sheet module. You might need to change the column number, to match the column number on your worksheet.
NOTE: The sample workbook also has code for clearing multiple dependent cells.
Download the Sample Files
Don't Miss Our Excel Tips
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
More Tutorials
Try-it!Transcript
Sort data in Excel quickly, in just a few clicks. To change the order of your data, sort it. To focus on a specific set of data, filter a range of cells or a table.
Select the data that you want to sort
Sort quickly
Want more?
Excel is an amazing tool for analyzing data.
And Sort and Filter are some of the most commonly used features to help you do this.
To change the order of your data, you’ll want to sort it.
Right-click a cell in the column you want to sort, and point to Sort.
Since the cells in this column contains only numbers, the sort options are Sort Smallest to Largest, and Sort Largest to Smallest.
Most number formats, such as Time and Currency, have the these sort options.
I click Sort Smallest to Largest, and the codes are sorted from lowest to highest value.
To focus on a specific set of your data, you can filter a range of cells or a table.
Click any cell in the range or table. On the HOME tab, click Sort & Filter, and click Filter.
Xls Fruit Composition Database List
Click a drop-down arrow at the top of one of the columns to display its filter options.
I click the drop-down arrow in the Category column.
Since the column contains text I get the Text Filters options. I can also uncheck and check values.
Xls Fruit Composition Database Examples
I uncheck all values, select Fruit, and click OK. And only the rows that have Fruit in the Category column are displayed.
Fruit Composition Table
Up next, Sort details.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |