Limit the choices in an Excel drop down list, by using named ranges and the INDIRECT function, to create dependent data validation lists. For example, select Fruit in column A, and only Fruit items appear in the drop down list in column B
In this dependent data validation example, if a country is selected from the first drop down, only that country's cities appear in the next column's drop down list.
However, you can use the Excel IF function to make the selection more flexible. If no country is selected, the City column will show a list of world cities in the dependent drop down list.
Start by creating Named Lists, which will be the choices in the dependent
data validation drop down lists. In this example, the first list will
be named Produce.
It contains the Produce categories -- Fruit and Vegetable.
Next, data validation will be applied on the data entry sheet
1. Apply the Excel Data Validation
Note: If cell A2 is empty, you'll see the message shown below. Click Yes to continue.
NOTE: If a Produce Type has not been selected, the Item drop down in that row will not work.
You could add another set of dependent data validation dropdown lists that depend on the selections in the first two dropdowns. For example, select a country and region, then select a city in that region.
You may 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'
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:
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.
Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation won't work with dynamic lists. Instead, you can use the following method:
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)
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.
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.
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.
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.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 2 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Last updated: April 8, 2015 7:45 PM