How to create a drop down list from another drop down list in Excel?
In this tutorial, I introduce the way to create a drop down list from another drop down list as below screenshot shown in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
For example, here are some country names, and every country has its own scenic spots, and now I want to create the first drop down list about country name, and then create the second drop down list about the scenic spots, which means that, I only can choose the scenic from of the second drop down list based on the selected country in first drop down list.
|Kutools for Excel, with more than 120 handy functions, makes your jobs easier.|
1. Select the country names, go to the Name box, give a name for the range, press Enter key. See screenshot:
2. Define scenic cells of each country as named range and name with a country name. See screenshot:
3. Then select a cell you want to create the first drop down list, and click Data > Data Validation > Data Validation. See screenshot:
4. In the Data Validation dialog, under the Settings tab, choose List from the Allow section, and type =Country into the Source textbox, Country is the range name you give to the counties in step 1. See screenshot:
5, click OK. The first drop down list has been created.
Now to create the second drop down list.
6. Select a cell you want to create the second drop down list, click Data > Data Validation > Data Validation.
7. In the Data Validation dialog, under the Settings tab, choose List from the Allow list, and type =INDIRECT($A$5) into the Source textbox, $A$5 is the cell you create the first drop down list in. See screenshot:
8. click OK.
Now the second drop down list has been created. See screenshots:
Alternate Row/Column Shading (quickly shade alternate row/columns as you need.)
- How to color coded drop down list in Excel?
- How to set/show preselected value for drop down list in Excel?
- How to auto update drop down list in Excel?