Hide Used Items in Excel Drop-Down Lists with WPS Spreadsheets
Question details
The user needs dependent data-validation lists that remove options already selected in earlier cells of the same row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A master option list is used to create several drop-down lists across a row, and each later list should exclude values already chosen.
- Observed behavior
- The drop-down lists continue to display options that have already been selected in the row.
Place the master options in a continuous range such as H2:H4, and reserve a blank helper area beginning at J2 for the filtered results. Confirm that your WPS Spreadsheets version supports the FILTER and COUNTIF functions before setting up data validation.
Use FILTER and COUNTIF to hide previously selected drop-down items
Use this method when your WPS Spreadsheets version supports dynamic array formulas and spill ranges.
A helper formula filters the master list by checking whether each option appears in the selections already made across the row. The spilled result can then be used as the data-validation source for the corresponding drop-down cell.
Select J2 and enter =FILTER($H$2:$H$4,COUNTIF($A2:$C2,$H$2:$H$4)=0), then press Enter. The helper range displays only options not found in A2:C2.
Select the target cell, such as A2, open the Data tab, choose Data Validation, select List as the validation type, and set the source to =$J$2# if your version accepts spill references.
For a later cell such as B2, change the COUNTIF range in its helper formula so it checks only the earlier selections, such as $A2:A2. Exclude the current cell from the checked range so its existing value is not filtered out.
Copy the helper formulas and data-validation settings across the target row. Verify that each later drop-down removes values selected in the cells before it.
Build a helper list when spill references are unavailable
Use a helper-column approach if your WPS version does not accept a dynamic spill reference such as J2# in Data Validation.
Create Dynamic Dependent Drop-Down Lists in WPS Spreadsheets
WPS Spreadsheets can create dependent data-validation lists with FILTER and COUNTIF formulas, allowing each new selection to remove the same option from later drop-downs. Its familiar spreadsheet interface also supports common Excel workbook formats for easy migration.
- 1. Prepare the source list: Enter the master options in a continuous range, such as H2:H4, and keep a blank helper area available beside the worksheet.
- 2. Filter unused options: In the helper area, enter =FILTER($H$2:$H$4,COUNTIF($A2:$C2,$H$2:$H$4)=0) to return options not yet selected in the row.
- 3. Set data validation: Select the target cell, open Data > Data Validation, choose List, and use the helper spill range, such as =$J$2#, as the source.
- 4. Copy and customize the setup: Copy the formulas and validation across the row, adjusting each COUNTIF range to include only the preceding selection cells.
Frequently Asked Questions
What formula removes used items from an Excel or WPS drop-down list?
Use =FILTER($H$2:$H$4,COUNTIF($A2:$C2,$H$2:$H$4)=0), replacing H2:H4 with the master list and A2:C2 with the row range containing existing selections.
How do I use the filtered results in Data Validation?
Place the FILTER formula in a helper cell such as J2, then open Data > Data Validation for the target cell, choose List, and enter the spill reference =$J$2# as the source.
Why does the current selection disappear from its own drop-down list?
The COUNTIF range may include the cell being validated. For each column, change the checked range to include only earlier cells, excluding the current cell from the formula.
What can I do if WPS does not accept the J2# spill reference?
Return the filtered values in a helper range, define a named range covering those results through Formulas > Define Name, and use that named range as the Data Validation list source.
