How to Display Different Excel Columns Based on a User Selection in WPS Office Spreadsheets
Question details
The user needs to return different columns from a table based on a selected form type by using the SWITCH and CHOOSECOLS functions to create a dynamic array that updates when the selection changes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table with multiple columns and a form type selector, and wants a live dynamic array that shows only the relevant columns for each form type.
- Observed behavior
- The formula should update automatically when the user selects another option, returning the corresponding column set without manual column hiding or filtering.
Verify that your version of WPS Office Spreadsheets supports dynamic array functions such as SWITCH and CHOOSECOLS, and check that the table reference and form type cell are named or referenced correctly.
Use SWITCH and CHOOSECOLS to Return the Selected Columns
Use this solution when the columns you want to return vary by form type and may be non-contiguous.
This formula builds a list of column indexes with SWITCH and then passes that list to CHOOSECOLS. The result is a dynamic array that spills into the cells to the right of the formula.
Click the cell where you want the dynamic array result to start, such as the first cell of an empty area on your worksheet.
Type =LET(tableIndices,SWITCH(formType,"Standard",{2,3,4},"Exchange",{5,8},"Return",{1,6,7,9}),CHOOSECOLS(table,tableIndices)) and replace formType with the cell containing the selection and table with your table reference.
Press Enter, then change the formType cell to Standard, Exchange, or Return to confirm that the returned columns update automatically.
Use DROP and TAKE When the Required Columns Are Contiguous
Use this alternative when the columns you need are adjacent and you only need to remove or keep a fixed number of columns.
Use WPS Office Spreadsheets to Build SWITCH and CHOOSECOLS Dynamic Reports
WPS Office Spreadsheets supports dynamic array functions, so you can use SWITCH and CHOOSECOLS to create a live report that shows different Excel columns whenever the selected form type changes.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx file that contains the table and the form type selector.
- 2. Select the output cell: Click an empty cell where the dynamic column result should begin.
- 3. Enter the SWITCH and CHOOSECOLS formula: Type the LET, SWITCH, and CHOOSECOLS formula and replace formType and table with your actual workbook references.
- 4. Press Enter and test the selector: Press Enter, then change the selection to verify that the columns spill and update automatically.
- 5. Save the workbook: Save the file in .xlsx or WPS format to preserve the formula and maintain Excel compatibility.
Frequently Asked Questions
Does this formula work in older versions of Excel or WPS Office?
No. CHOOSECOLS and dynamic array formulas require a version that supports them. In WPS Office Spreadsheets, use a current version and keep the spill area clear.
What should I do if I get a #SPILL! error?
Clear the cells to the right of and below the formula output so the dynamic array has enough room, or move the formula to a larger empty area.
Can I use a dropdown list for the form type selection?
Yes. Use Data > Data Validation to create a list with values such as Standard, Exchange, and Return, and refer to that cell as formType.
Do the column indexes have to be in the original column order?
No. The array constant can be in any order, and CHOOSECOLS will return the columns in the order you specify.
Can I use a table name instead of a range?
Yes. If your data is in an Excel table, replace table with the table name or a structured reference such as Table1 for CHOOSECOLS to use.
