Excel’s INDIRECT function is a powerful tool for creating dynamic drop-down lists based on the selection from another drop-down list. This allows you to build cascading or dependent lists, where the choices in the second list depend on the selection made in the first list.

Here’s how you can set it up:

Step 1: Create the Source Lists

Begin by creating two separate lists in your Excel worksheet:

1. Source List 1: This list contains the categories or items that will populate the first drop-down list. Let’s assume you have these items in cells A1 to A5.
2. Source List 2: Create multiple lists for each category from Source List 1. These lists will be used to populate the second drop-down list based on the selection from the first drop-down list. For example, you can have lists for each category starting from cell B1 for category 1, C1 for category 2, and so on.

Step 2: Create the First Drop-Down List

1. Select the cell where you want the first drop-down list.
2. Go to the “Data” tab in the Excel ribbon.
3. Click on “Data Validation”.
4. In the “Data Validation” dialog box, under the “Settings” tab:
   • Set “Allow” to “List”.
   • In the “Source” field, enter =A1:A5 to refer to your Source List 1.
5. Click “OK”. Now, you have a drop-down list based on the categories from Source List 1.

Step 3: Create the Second Drop-Down List (Dynamic)

1. Select the cell where you want the second drop-down list.
2. Again, go to the “Data” tab and click on “Data Validation”.
3. In the “Data Validation” dialog box, under the “Settings” tab:
   • Set “Allow” to “List”.
   • In the “Source” field, enter =INDIRECT(B1).
4. Click “OK”. Now, the second drop-down list will dynamically change based on the selection from the first drop-down list. The INDIRECT function refers to the cell in which the category from the first drop-down list is selected and uses it as a reference to display the corresponding list from Source List 2.

Step 4: Test the Drop-Down Lists

Now, when you select a category from the first drop-down list, the second drop-down list will change accordingly to display the items from the selected category.

This method allows you to create cascading or dependent drop-down lists in Excel, where the choices in the second drop-down list depend on the selection made in the first drop-down list. It’s a powerful way to organize and manage data in Excel.

Related posts:

How to insert drawing objects in Excel How to remove cell border color League of Legends Tracker Spreadsheet