How to Filter an Excel Data Validation List Based on Another Cell
Question details
The user wants to create a dependent data validation drop-down list that dynamically filters options based on the value selected in another cell.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating dynamic and interconnected drop-down lists for accurate data entry, where the options available in a secondary list depend on a preceding selection (e.g., selecting a Site filters the available Equip IDs).
- Observed behavior
- The goal is to automatically update the drop-down list's options whenever the parent cell's value changes.
Ensure your source data is organized in a structured table or named range. You will need a blank column or area in your worksheet to act as a helper range for the dynamic spilled array.
Use the FILTER Function to Create a Dependent Drop-Down List
Extract matching values into a helper range using the FILTER function, then link the drop-down list to that dynamic spilled array.
This method uses dynamic array formulas to automatically adjust the list of options. It replaces the traditional, more complex INDIRECT function method and avoids manual named range updates.
Ensure your main dataset is formatted as a table (e.g., named 'tblequip') containing categorized columns, such as 'Site' and 'Equip ID'.
In an empty cell (for example, H2), enter the formula =FILTER(tblequip[Equip ID], tblequip[Site]=G18), where G18 is the cell containing your primary drop-down selection.
Select the cell where you want the dependent drop-down list (e.g., F7). Go to the Data tab on the ribbon, select 'Data Validation', and choose 'List' from the Allow drop-down menu.
In the Source box, enter the reference to your helper cell followed by a hash symbol (e.g., =H2#). Click OK to apply the validation. The list will now update automatically when G18 changes.
Create Dependent Drop-Down Lists Easily in WPS Spreadsheet
WPS Spreadsheet fully supports dynamic arrays and the FILTER function, allowing you to create dependent data validation lists quickly without complex workarounds. Enjoy advanced data management with a seamless and free office suite.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, open your workbook, and organize your dataset into clear reference columns.
- 2. Apply the FILTER formula: Select a blank helper cell and type =FILTER(return_array, lookup_array=target_cell) to generate a dynamic list based on the main selection.
- 3. Access Data Validation: Click on your target input cell, navigate to the 'Data' tab on the top ribbon, and click 'Validation'.
- 4. Link the dynamic range: Select 'List' in the criteria settings, input your helper cell reference with a '#' symbol (e.g., =H2#) in the Source field, and click 'OK'.
Frequently Asked Questions
Why is my dependent drop-down list showing a #CALC! error?
This happens when the FILTER formula returns no results because the primary selection cell is empty or has no matching records. You can fix this by using the optional [if_empty] argument in the FILTER function, such as =FILTER(A:A, B:B=C1, "No Options").
Can I create a dependent drop-down list without the FILTER function?
Yes. If you are using an older version of your spreadsheet software that doesn't support dynamic arrays, you can use the INDIRECT function combined with Named Ranges to achieve similar dependent drop-down functionality.
Does the hash (#) symbol work directly inside Data Validation?
Yes, the hash symbol (#) is used to reference a spilled array. When placed immediately after a cell reference (like =H2#) in the Data Validation Source field, it commands the drop-down menu to include all dynamically generated results from that starting cell.
Can I hide the helper column used for the FILTER formula?
Absolutely. Once your data validation list is correctly linked to the helper column and working, you can right-click the column letter at the top and select 'Hide'. The drop-down list will continue to function normally even if the reference column is hidden.
