How to Combine Data from Multiple Date-Named Sheets in Excel
Question details
The user needs to collect data from specific ranges across multiple worksheets that are named by dates, but their current formula encounters errors when copied vertically.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating daily reports or date-based datasets from multiple worksheet tabs into a single master summary sheet.
- Observed behavior
- The formula successfully retrieves data across columns but fails or returns incorrect values when copied down rows due to mixed absolute and relative references.
Ensure all your date-named worksheets follow a strictly consistent naming convention (e.g., 'YYYY-MM-DD') and have identical data structures to prevent array formula mismatch errors.
Use VSTACK and LET for Dynamic Array Consolidation
Leverage modern dynamic array functions to stack data across multiple sheets automatically without needing to manually copy formulas down.
In modern spreadsheet versions (like Microsoft 365 or newer WPS Spreadsheet), you can avoid dragging formulas entirely. Using VSTACK combined with LET makes your formula easier to read and automatically spills the results into neighboring rows.
Click on the top-left cell in your master summary sheet where you want the consolidated data to begin.
Type '=LET(data, ' to begin storing your repeated expressions into a variable named 'data'. This improves calculation speed.
Inside the LET formula, use VSTACK to stack the 3D references. For example: '=LET(data, VSTACK('01-Jan:31-Jan'!A2:D50), data)'. This formula grabs the range A2:D50 from all sheets between '01-Jan' and '31-Jan'.
Hit Enter. The formula will automatically spill down the rows and across the columns, eliminating the need for absolute/relative reference adjustments.
Fix Mixed Absolute and Relative Cell References
Adjust the dollar signs ($) in your existing formula so that it behaves correctly when dragged vertically and horizontally.
Use INDIRECT for Dynamically Generated Sheet Names
Use the INDIRECT function to construct sheet references dynamically based on the date text written in your master sheet's cells.
Seamlessly Combine Multiple Worksheets with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like VSTACK, MAP, and LET, making it incredibly easy to consolidate data across multiple date-named sheets without worrying about complex reference errors.
- 1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your date-named worksheets.
- 2. Select the master sheet: Navigate to the blank summary sheet where you wish to combine the data.
- 3. Enter the VSTACK formula: Type =VSTACK( followed by selecting the ranges across your date sheets holding Shift, then close the parenthesis.
- 4. Press Enter to execute: Hit Enter. WPS Spreadsheet will instantly process the array and spill your consolidated data into the grid.
Frequently Asked Questions
Why does my workbook slow down when I use the INDIRECT formula across multiple sheets?
INDIRECT is known as a volatile function. This means it recalculates every single time any change is made anywhere in the workbook, consuming significant processing power and causing noticeable performance drops in large files.
Can I combine data from multiple sheets without using INDIRECT?
Yes. If your sheets are positioned next to each other in the workbook, you can use 3D references (e.g., =SUM('01-Jan:31-Jan'!A1)). In modern spreadsheet versions, using the VSTACK function allows you to stack these 3D references cleanly without volatility.
Why does my formula break when I drag it down the column but works when dragged across?
This happens due to missing or incorrect absolute cell references. To fix this, you must lock the row references in your formula using the $ symbol (for example, changing A1 to A$1) so the row target doesn't shift downwards as you drag.
What is the #SPILL! error when using VSTACK or LET?
The #SPILL! error indicates that a dynamic array formula (like VSTACK) does not have enough empty cells below or to its right to display all the calculated results. Clear the obstructing data to allow the formula to spill correctly.
