How to Build a Dynamic VSTACK List from Excel Table Names in WPS Spreadsheets
Question details
The user wants to combine rows from multiple Excel tables into one dynamic list by reading table names from a worksheet list and using VSTACK, REDUCE, and INDIRECT.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook stores table names in a table such as Tabelle5[Table]. The user needs a formula that loops through every name, retrieves the corresponding table, and appends its rows into a single spill range.
- Observed behavior
- Instead of a manual copy-and-paste workflow, the user needs a single formula-driven list that updates automatically when a table is added, removed, or edited.
Open the workbook in WPS Spreadsheets and verify that every name in the table-name list matches an existing Excel table name exactly. If any source table is stored on another sheet or has a name with spaces, adjust the INDIRECT reference or table naming before entering the formula.
Stack Named Excel Tables Dynamically with REDUCE, SEQUENCE, INDIRECT, and VSTACK
Use this array formula when the source table names are stored in a worksheet list and you want new source tables automatically included as the list grows.
This method uses SEQUENCE(ROWS()) to iterate over the entire table-name list, INDEX to read one name at a time, INDIRECT to turn that name into a table reference, and VSTACK to append all returned rows into one spill range. It is best when the list of tables changes frequently and you do not want to edit the formula each time.
Open WPS Spreadsheets and go to the worksheet that holds your Excel table names in a single column. In the example workbook, this is the Tabelle5[Table] column, but you can select any one-column table range that contains the names of the tables you want to stack.
Click the blank cell where the stacked list should begin. Leave enough empty cells below and to the right so the VSTACK spill range can expand without overwriting existing data.
Type =REDUCE({"Name","City","Rng"},SEQUENCE(ROWS(Tabelle5[Table])),LAMBDA(x,y,VSTACK(x,INDIRECT(INDEX(Tabelle5[Table],y,1))))) and press Enter. Replace Tabelle5[Table] with your actual table-name list, and change the header array {"Name","City","Rng"} to match the columns returned by your source tables.
Add a new table name to the list or change a row in one of the source tables. Check that the spilled range recalculates and includes the updated data automatically.
Create a Self-Updating Stacked Table List in WPS Spreadsheets
WPS Spreadsheets supports dynamic array formulas such as VSTACK, REDUCE, SEQUENCE, and INDIRECT, so you can build one formula that reads the table-name list, retrieves each Excel table, and stacks the rows automatically.
- 1. Open the workbook: Launch WPS Office, click Menu > Open, and select the .xlsx workbook that contains the Excel tables and the worksheet list of table names.
- 2. Check the table-name list: Go to the table-name worksheet and confirm it contains one table name per row, with no extra spaces or invalid characters that would prevent INDIRECT from resolving the table.
- 3. Enter the formula: Click the blank cell where the dynamic list should appear, type the REDUCE/VSTACK/INDIRECT formula from this page, and adjust the table-name list and header row to match your workbook.
- 4. Review the spill range: Press Enter and verify that WPS Spreadsheets returns the stacked rows. If any row is missing, check that the table name matches the name shown in Name Manager.
- 5. Append new names: Add another table name to the bottom of the table list and confirm the stacked output automatically expands to include the new table.
Frequently Asked Questions
Why does my formula return #NAME? or #REF! when using VSTACK?
Check that your WPS Office version supports dynamic array functions and that the table names in the list exactly match the real Excel table names. Open Formulas > Name Manager to verify each name; for sheet names with spaces, update the INDIRECT reference accordingly.
Can I stack Excel tables that have different columns?
VSTACK can combine arrays, but mismatched columns can produce empty or fill values. For a clean result, keep the same column order and number of columns across all source tables, and match the header row in the REDUCE formula to that layout.
How does the formula update when I add a new table name?
The formula uses SEQUENCE(ROWS(Tabelle5[Table])) to run one iteration per row in the table-name list. Because the list is referenced as a table range, adding a name to the next row automatically increases the sequence and the VSTACK output expands.
Do I have to enter this as an array formula?
In WPS Spreadsheets with dynamic array support, you can enter it normally in the top-left output cell and press Enter. It spills into the adjacent cells; no legacy Ctrl+Shift+Enter is required if dynamic arrays are enabled.
Can I use this technique with sheet names instead of table names?
INDIRECT works with text references, so you can adapt the list to store sheet and range strings, but table names are more reliable because they adjust when data moves and show meaningful names in Name Manager.
