How to Create Dynamic Dependent Lists with Excel FILTER
Question details
The user needs to create a dynamic dependent list returning matching subcategories for category headers across columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a data validation dropdown list that updates dynamically based on the category selected in a header cell.
- Observed behavior
- The FILTER function may return an error if the category reference does not perfectly align with the table data or if Data Validation restricts spilled arrays.
Ensure your source data is formatted as an official Excel Table (Insert > Table) named 'StdCategories', and verify that your category headers exactly match the data entries without leading or trailing spaces.
Use SORT and FILTER for Dynamic Lists
Apply the FILTER function to extract subcategories based on a specific category header, wrapping it in SORT for alphabetical ordering.
This method extracts a clean, alphabetized list of subcategories that dynamically updates if the source table changes. It is the most robust way to handle dynamic arrays for dependent dropdowns.
Select the first cell where you want the dependent list array to spill (e.g., cell E2).
Type the formula `=SORT(FILTER(StdCategories[Subcategory],StdCategories[Category]=E$2,"None"))` into the formula bar.
Press Enter. The matching subcategories will spill down the column automatically.
Drag the fill handle to the right to apply the formula across other category headers.
Generate a Full Two-Dimensional Array
Use advanced array functions to build a complete dynamic grid of categories and subcategories if you have a manageable number of columns.
Create Dynamic Lists in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, SORT, and UNIQUE. You can easily build dynamic dependent dropdowns and manage your data validations seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open your dataset containing the categories and subcategories.
- 2. Apply the FILTER formula: Type your `=FILTER(...)` formula in a blank cell to generate the spilled dynamic list.
- 3. Set up Data Validation: Go to the Data tab, click 'Validation', choose 'List', and reference your spilled array using the hashtag symbol (e.g., =$E$2#).
Frequently Asked Questions
Why is my FILTER formula returning a #CALC! error?
This happens when the FILTER function finds no matching records and the optional [if_empty] argument is missing. Ensure you add a fallback value, such as "None", at the end of your formula: =FILTER(array, include, "None").
Can I use spilled arrays directly in Data Validation?
Yes, you can reference a spilled array in the Data Validation source box by adding a hashtag to the top-left cell reference (e.g., =$E$2#). However, some older spreadsheet iterations may require you to define a Named Range first.
Why are my dependent dropdown formulas showing a #SPILL! error?
A #SPILL! error means the formula needs multiple cells to display the results, but one or more of those destination cells are not empty. Clear the blocking cells or text to allow the dynamic array to populate fully.
Does the header text need to match the category column exactly?
Yes. The text in your category header cell must perfectly match the category column in your source table. Check for hidden spaces in your data and use the TRIM function if necessary to clean the text.
