Create Dynamic WPS Spreadsheet Table References from Drop-Down Dates
Question details
Create dynamic table references based on start and end date headers selected from drop-down lists.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants summary tables to pull a changing range of columns from a table named WeeklyAvgs, including a previous-period range.
- Observed behavior
- A fixed structured reference does not update when the selected start and end date headers change.
Ensure the source data is formatted as a table named WeeklyAvgs and that the drop-down cells contain header text that exactly matches the table column headers. Place the selected start and end headers in cells such as C1 and C2.
Build a dynamic structured reference with INDIRECT
Use this method when your selected start and end headers should define the columns returned from the WeeklyAvgs table.
INDIRECT converts text into a usable table reference. The selected header names must match the WeeklyAvgs column headers exactly, including dates and punctuation.
Select the cells for the start and end selections, such as C1 and C2, then use Data > Data Validation to create drop-down lists containing the WeeklyAvgs date header names.
In the summary formula location, replace the fixed reference with =INDIRECT("WeeklyAvgs[["&$C$1&"]:["&$C$2&"]]"). This builds a reference from the headers selected in C1 and C2.
Wrap the dynamic reference in the calculation you need. For example, enter =SUM(INDIRECT("WeeklyAvgs[["&$C$1&"]:["&$C$2&"]]")) to total the columns between the selected headers.
Use separate cells for the previous-period start and end header names, then use the same pattern with those cells: =INDIRECT("WeeklyAvgs[["&$E$1&"]:["&$E$2&"]]"). Update E1 and E2 from your period logic or previous-header selections.
Create Date-Driven Summary Ranges in WPS Spreadsheet
WPS Spreadsheet lets you use Data Validation drop-down lists and the INDIRECT function to turn selected date headers into dynamic table ranges. You can apply the same approach to current and previous-period summaries without manually editing fixed references.
- 1. Open the worksheet in WPS Spreadsheet: Open your workbook and confirm that the source range is available as the WeeklyAvgs table.
- 2. Add date header selectors: Select the start and end input cells, then choose Data > Data Validation to create the date-header drop-down lists.
- 3. Insert the dynamic reference: Click the summary cell and enter =INDIRECT("WeeklyAvgs[["&$C$1&"]:["&$C$2&"]]") using your selector cells.
- 4. Apply a summary function: Add a function such as SUM, AVERAGE, or COUNT around the INDIRECT reference according to the result required.
Frequently Asked Questions
Why does my dynamic table reference return an error?
Check that the table name is exactly WeeklyAvgs and that the text selected in C1 and C2 exactly matches existing table headers. A missing header, extra space, or different date display can make INDIRECT return an invalid reference.
Can I use a dynamic reference inside SUM or AVERAGE?
Yes. Enter a function around the reference, such as =SUM(INDIRECT("WeeklyAvgs[["&$C$1&"]:["&$C$2&"]]")) or =AVERAGE(INDIRECT("WeeklyAvgs[["&$C$1&"]:["&$C$2&"]]")).
How do I create a previous-period summary range?
Store the previous period's start and end header names in separate cells, such as E1 and E2, then point the formula to those cells: =INDIRECT("WeeklyAvgs[["&$E$1&"]:["&$E$2&"]]").
Does the start header need to appear before the end header?
Yes. In the WeeklyAvgs table, the header selected as the start boundary should be positioned to the left of the end boundary so the structured reference defines a valid column range.
Why can INDIRECT make a workbook slower?
INDIRECT is volatile, meaning it recalculates whenever the workbook recalculates. Limit repeated INDIRECT formulas when working with large tables or many worksheets.
