Excel - Cascading drop-down list
The aim of this tip is to create a drop-down list to select a category in a cell A and provide in cell B another drop-down that will be based on your previous choice. We will make use of Data/Data Validation/List option and the INDIRECT function.
For the first drop-down list use the procedure depicted here.
Step 1 = Prepare range of cells containing the different lists.
- Enter a list of departments and name this range of cell as Dep: Guadeloupe in B2, Guyana in C2, Martinique in D2 , Reunion in E2...etc
- In B3, B4, B5 etc. enter in alphabetical order, the names of the 34 towns of Guadeloupe: Abymes, Anse-Bertrand, Baie-Mahault, etc. Name this range of cell as Guadeloupe]
- In C3, C4, C5 etc. enter in alphabetical order the names of the 22 towns of Guyana. Name this range of cell as [Guyana].
- Repeat the operation accordingly for the other departments
Step 2 = Create the first drop-down list, that displays the name of the department.
- In another sheet of your workbook, select cell B1
- Go to the Data tab> Data Validation > Setting tab.
- Under "Allow" select "list"
- In the Source field enter "=Dep"
- Name cell B1 as "Department" (right-click on B3 > Name a range)
Create your second drop-down list
- Create another dropdown list in B3 and in the source field enter this formula =INDIRECT(Department)
- Start by selecting a department in B1, then verify that the dropdown list in B3 displays the towns present in the selected department.
Related
- Liste en cascade excel
- Liste excel en cascade - Best answers
- Excel liste en cascade - Best answers
- Liste déroulante en cascade vba userform - How-To - Excel
- Liste en excel - How-To - Excel
- How To Create and Name Worksheets Based on a List via an Excel Macro - How-To - Excel
- Moyenne en anglais excel - How-To - Excel
- Passer de majuscule en minuscule excel - How-To - Office Software
This document, titled « Excel - Cascading drop-down list », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
Subscribe To Our Newsletter!
