How to Populate a WPS Spreadsheet from a Column-Based Drop-Down List
Question details
Create a data validation drop-down list from column headers and return the nonblank names from the selected column to another worksheet.
- Product
- WPS Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user has a source worksheet with column headers in row 1 and values under each header. On another worksheet, they want to select a header from a drop-down and have the matching nonblank values appear automatically.
- Observed behavior
- The desired result is a dynamic list that updates when a different column header is selected in the drop-down.
Arrange the source data with the column headers in Sheet1!A1:C1 and the list values directly below those headers. In the output sheet, set aside cell A2 for the selected drop-down header and make sure the area below the formula is clear for spilled results.
Create the column-header drop-down list and spill nonblank values with FILTER
Use this method when your WPS Spreadsheets or Excel version supports the FILTER dynamic array function; it is the fastest way to return all nonblank names from the selected column.
The validation list exposes the column headers from Sheet1. The formula matches the selected header in Sheet2!A2 to a column in Sheet1, then FILTER spills only the nonblank cells from that column.
In Sheet2, select cell A2, go to Data > Validation or Data > Data Validation, choose List under Allow, set Source to =Sheet1!$A$1:$C$1, and click OK.
Select the first cell where the returned names should appear, enter =FILTER(INDEX(Sheet1!$A:$C,0,MATCH(A2,Sheet1!$A$1:$C$1,0)),INDEX(Sheet1!$A:$C,0,MATCH(A2,Sheet1!$A$1:$C$1,0))<>"") and press Enter.
Change the drop-down selection in cell A2 to a different column header and confirm that only that column's nonblank values spill into the output area.
Populate a Column-Based Drop-Down List in WPS Spreadsheets
WPS Spreadsheets handles the same data validation list and dynamic array formula, so selecting a column header from a drop-down can automatically return all nonblank values from that column.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open your .xlsx workbook, and keep Sheet1 as the source data.
- 2. Set up the drop-down list: On Sheet2, click cell A2, go to Data > Validation, select List, and enter =Sheet1!$A$1:$C$1 as the source.
- 3. Enter the lookup formula: In the first output cell, enter =FILTER(INDEX(Sheet1!$A:$C,0,MATCH(A2,Sheet1!$A$1:$C$1,0)),INDEX(Sheet1!$A:$C,0,MATCH(A2,Sheet1!$A$1:$C$1,0))<>"").
- 4. Verify the dynamic result: Select a different header from the drop-down and confirm that the listed names update to show only that column's nonblank values.
Frequently Asked Questions
Can I use this column-based drop-down method in WPS Spreadsheets?
Yes. Open your workbook in WPS Spreadsheets, create the list through Data > Validation, and use the same FILTER/INDEX/MATCH formula in the output cell.
Why does the formula return a #SPILL! error?
The spill range must be empty for the dynamic array to expand. Remove any data placed below the formula cell, then the values should appear normally.
Can I use a drop-down source that is not in row 1?
Yes. The source range can point to any header row, but the MATCH range in the formula must match the same header range so the selected header is found correctly.
How do I show only blank-free values from the selected column?
The recommended formula uses the condition <>"" inside FILTER, which excludes empty cells and returns only the nonblank entries from the matched column.
