Function Problems

How to Dynamically Reference Monthly Tables in Excel Using Formulas

Camila Milosovich Oct 16, 2026 868 views

Question details

The user needs to build a rolling 12-month summary report by dynamically referencing data stored in separate, uniquely named monthly tables.

How to Dynamically Reference Monthly Tables in Excel Using Formulas

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Creating a dynamic rolling 12-month summary where data for each month is separated into individual structured tables, requiring the formula to select the correct table based on a cell value automatically.
Observed behavior: Standard formulas cannot directly read a text string from a cell as a structured table name without using specific functions like INDIRECT to evaluate the text as a reference, or by altering the data model.

Before you start

Ensure that all your separate monthly tables share the exact same column structure and headings, as this consistency is required for dynamic referencing and formula-based summaries to work correctly without errors.

Solution 1Recommended

Use the INDIRECT Function for Dynamic Table Referencing

The INDIRECT function allows you to convert a text string from a cell into a valid table reference, making it possible to dynamically select different monthly tables in your formulas.

While Excel formulas cannot directly use a plain cell value as a structured table name, the INDIRECT function evaluates text strings and translates them into actual functional references. Keep in mind that INDIRECT is a volatile function; if applied across thousands of rows, it may slow down performance.

Set up your reference cell

Type the exact name of your target monthly table (e.g., 'Table_Jan') into a specific cell, such as A1.

Write the INDIRECT formula

In your summary cell, wrap the cell reference inside an INDIRECT function combined with your desired calculation, for example: =SUM(INDIRECT(A1 & "[Sales]")).

Generate a rolling 12-month date sequence

To automatically generate the monthly date range for your report's timeline, use the formula: =EDATE(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),ROW(A1)-1) and drag it down across 12 rows.

Use the INDIRECT Function for Dynamic Table Referencing

Performance Tip: Because INDIRECT recalculates every time any change is made to the workbook, you may want to consider consolidating your data using alternative methods if your spreadsheet becomes noticeably slow.

Solution 2

Consolidate Monthly Tables with Power Query

For better performance in a rolling 12-month report, use Power Query to append all monthly tables into one master table instead of using complex volatile dynamic formulas.

Efficient Data Management Tools

Easily Consolidate and Analyze Monthly Tables in WPS Spreadsheet

WPS Spreadsheet provides robust functions and data handling capabilities to help you seamlessly build rolling 12-month summaries and dynamically reference structured tables without lag.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the separate monthly data tables.
2. Set up your dynamic timeline: In your summary sheet, enter =EDATE(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),ROW(A1)-1) to instantly populate your rolling 12-month dates.
3. Apply dynamic references with INDIRECT: Use the INDIRECT function to dynamically pull data from your separate monthly tables based on the table names matching your generated dates.

Fully compatible with Microsoft Excel formulas, structured table references, and the INDIRECT functionLightweight architecture ensuring fast calculation speeds even with complex dynamic formulasBuilt-in intuitive data analysis tools to create monthly summaries quickly and reliablyFree to download and use with a highly familiar, easy-to-navigate tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just type a cell reference into a table name formula?

Standard spreadsheet formulas expect hardcoded, actual structural references. When you point to a cell that contains a table's name, the software reads it as plain text, not a location. You must use the INDIRECT function to force the program to evaluate that text string as a valid structured table reference.

What is the EDATE function doing in the monthly sequence formula?

The EDATE function calculates a date that is a specified number of months before or after a starting date. In the formula =EDATE(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),ROW(A1)-1), it dynamically calculates the date exactly one year ago from the current month, and sequentially lists consecutive months as you drag the formula down the rows.

Is it better to keep monthly data in separate tables or one master table?

It is generally much more efficient and less prone to calculation errors to keep all your data in a single master table with a dedicated 'Month' or 'Date' column. This structure allows you to use simple, lightweight SUMIFS functions or PivotTables, entirely avoiding the need for volatile functions like INDIRECT across multiple separate tables.

Excel & Data Problems