WPS Spreadsheet: Reference a Dynamic Excel Table Column Without VBA
Question details
The user needs to make a formula reference different Excel table columns based on the header selected in cell U4, without using macros or VBA.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A workbook contains a table named Table1, and the selected table-column header is entered in cell U4. The formula should use that selection dynamically.
- Observed behavior
- The required table column must change automatically when the value in U4 changes, while the workbook remains macro-free.
Confirm that the table is named Table1 and that U4 exactly matches one of its column headers, including spaces and punctuation. Check whether your WPS Spreadsheet version supports XLOOKUP and FILTER before choosing an alternative to INDIRECT.
Use INDIRECT with the selected table header
Use this method when U4 exactly matches a Table1 column header and the formula needs to build a structured reference dynamically.
INDIRECT converts text into a cell or range reference, allowing the value in U4 to determine which Table1 column is used. Because INDIRECT is volatile, frequent recalculation may affect performance in large workbooks.
In WPS Spreadsheet, click the cell where you want the result to appear.
Type =SUMIFS(INDIRECT("Table1["&U4&"]"),Table1[Category],U4) and press Enter.
Change the header text in cell U4 to another exact Table1 column name and verify that the result updates automatically.
Return the selected table column with XLOOKUP
Use this option when your WPS Spreadsheet version supports XLOOKUP and you want to return the entire table column selected in U4.
Filter records using the dynamically selected column
Use this method when you need to return matching records after selecting the comparison column through U4.
Build Dynamic Table Formulas in WPS Spreadsheet Without VBA
WPS Spreadsheet lets you create macro-free formulas that respond to a selected table header. Use INDIRECT for broad compatibility, or XLOOKUP and FILTER when supported, while keeping your workbook easy to edit and share.
- 1. Open the workbook: Launch WPS Office, open your spreadsheet, and select the worksheet containing Table1.
- 2. Confirm the table structure: Click inside the table and check the table name, column headers, U4 selection cell, and any criteria cell such as Y4.
- 3. Add the dynamic formula: Select the result cell and enter the INDIRECT, XLOOKUP, or FILTER formula that matches your workbook goal.
- 4. Test the result: Change U4 to another valid table header and confirm that the returned column, total, or filtered records update.
Frequently Asked Questions
What must be entered in cell U4 for the dynamic formula to work?
U4 must contain the exact text of a Table1 column header. Differences in spelling, spaces, or punctuation can cause INDIRECT or XLOOKUP to return an error or no match.
Can I reference a dynamic table column without VBA in WPS Spreadsheet?
Yes. Use =SUMIFS(INDIRECT("Table1["&U4&"]"),Table1[Category],U4) for a calculated result, or use =XLOOKUP(U4,Table1[#Headers],Table1) to return the selected column when XLOOKUP is supported.
Why might INDIRECT slow down a large workbook?
INDIRECT is a volatile function, so it recalculates whenever the workbook recalculates. In a large workbook, XLOOKUP may provide a more efficient alternative if your WPS Spreadsheet version supports it.
How can I filter rows using the column selected in U4?
Use =FILTER(Table1[[name]:[Contact ID]],XLOOKUP(U4,Table1[#Headers],Table1)=Y4,"No records found"). Enter the target table header in U4 and the matching value in Y4.
What happens if U4 does not match a Table1 header?
INDIRECT may return a reference error, while XLOOKUP may return a not-found error. Check the header text or add error handling such as IFERROR around the formula.
