Answer

Creating a multi-row dynamic dependent drop-down list in Excel allows users to select values in one column that automatically adjust the options in another column. This setup is common for managing categories and subcategories, such as selecting a product type and then a specific product.

Step 1: Organize Your Data

1. Create a clear table structure for your data.
   Example:

2. Name the ranges for each category to make formulas dynamic:

   • Select the subcategory list for Fruit and name it Fruit in the Name Box.

   • Select the subcategory list for Vegetable and name it Vegetable.

Step 2: Create the Primary Drop-Down List

1. Select the cells where you want the main category drop-down.

2. Go to Data → Data Validation → Data Validation.

3. Under Allow, select List.

4. In Source, enter your main categories separated by commas: 

   Fruit,Vegetable

5. Click OK.

Now, your first column has a drop-down list for the main category.

Step 3: Create the Dependent Drop-Down List

1. Select the cells where you want the subcategory drop-down.

2. Go to Data → Data Validation → Data Validation.

3. Under Allow, select List.

4. In Source, enter the formula: 

   =INDIRECT(A2)

   • Replace A2 with the first cell of your primary category column.

This formula uses the selection in the main category to pull the correct subcategory list dynamically.

Step 4: Make the List Multi-Row Dynamic

1. Select multiple rows in your subcategory column.

2. Apply the same Data Validation → List → =INDIRECT(A2).

3. Excel will automatically adjust the formula for each row, creating a dynamic dependent drop-down for multiple rows.

Step 5: Test the Drop-Down List

1. Select a category in the first column.

2. Click the drop-down in the adjacent cell. Only the subcategories corresponding to the chosen category will appear.

3. Repeat for multiple rows to confirm that the dynamic behavior works.

Step 6: Optional – Make it Fully Dynamic

1. Use Excel Tables to automatically expand the list when new data is added:

   • Convert your data into a table by pressing Ctrl + T.

2. Use the OFFSET function to make your named ranges dynamic: 

   =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

   This automatically adjusts the subcategory list if new items are added.

Notes on Windows vs Mac

• The steps for Data Validation are identical on both Windows and Mac.

• On Mac, the Name Box may behave slightly differently, but naming ranges work the same way.

Example Scenario

• Primary Drop-Down (Column A): Fruit, Vegetable

• Dependent Drop-Down (Column B):

  • If Fruit is selected → Apple, Banana, Orange

  • If Vegetable is selected → Carrot, Broccoli

This setup ensures that multiple rows can be used without manually entering formulas for each row.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report