'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Average Values Across Dynamic Year Sheets Using INDIRECT in Excel
Question details
The user wants to average a specific cell's values across a dynamic range of worksheets named by years. The start and end years are stored in specific cells, and the goal is to reference them dynamically instead of hard-coding the sheet range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating monthly totals or averages across multiple yearly worksheets where the time frame needs to update automatically when the start and end year cells are changed.
- Observed behavior
- When putting a cell reference like '!B34' inside quotation marks for the INDIRECT function, it becomes a static text string and fails to update to C34, D34, etc., when the formula is dragged across columns.
Ensure that your yearly worksheets are named exactly as the numerical years (e.g., 2008, 2009) and that the start and end years referenced in your formula actually exist as sheets in your workbook.
Use INDIRECT, SEQUENCE, and OFFSET for Dynamic Sheet References
This is the recommended approach for modern Excel versions. It dynamically generates an array of sheet names and uses OFFSET to allow column references to update when dragging the formula.
By utilizing the SEQUENCE function, Excel can automatically generate an array of numbers representing the years between your start and end dates. Wrapping this inside INDIRECT allows you to target those specific sheets.
Since any cell reference inside INDIRECT is formatted as a text string (e.g., "!B34"), it becomes absolute and will not adjust when you copy the formula across columns. Using the OFFSET function solves this limitation by shifting the reference dynamically based on the current column.
Enter your starting year in cell B56 and your ending year in cell C56 on your summary sheet.
Select the target cell where you want the average to appear and enter the following formula: =AVERAGE(OFFSET(INDIRECT("'"&SEQUENCE($C$56-$B$56+1,,$B$56)&"'!B34"),0,COLUMN(D1)-COLUMN($D$1)))
Press Enter to calculate the result. Click the fill handle in the bottom-right corner of the cell and drag it horizontally across your columns. The OFFSET function will correctly adjust the columns from B34 to C34, D34, etc.
Use ROW and INDIRECT for Legacy Excel Versions
If you are using an older version of Excel that does not support the SEQUENCE function, you can use the ROW function to generate the array of years.
Easily Manage Advanced Array Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical array functions, including INDIRECT, OFFSET, and dynamic sequences. You can easily calculate dynamic 3D averages across multiple year-named sheets using the exact same formulas while enjoying a smooth and lightweight working environment.
- 1. Open Your Workbook in WPS: Launch WPS Office and open your workbook containing the yearly worksheets.
- 2. Enter the Dynamic Array Formula: In your summary sheet, type the INDIRECT and OFFSET formula directly into the formula bar.
- 3. Drag to Autofill: Press Enter, then use the fill handle in the corner of the cell to drag the formula across your columns. WPS Spreadsheet calculates the dynamic columns perfectly.
Frequently Asked Questions
Why doesn't the cell reference change when I drag an INDIRECT formula across columns?
Because the cell reference inside the INDIRECT function is enclosed in quotation marks (e.g., "!B34"), Excel treats it as a static text string. It loses its relative properties, meaning it won't automatically update to C34 or D34 when dragged. Wrapping it in an OFFSET function compensates for this limitation.
What is the purpose of the OFFSET function in this dynamic sheet formula?
The OFFSET function dynamically shifts the target cell reference based on the column number. Since INDIRECT forces the base cell to remain static, OFFSET uses COLUMN(D1)-COLUMN($D$1) to calculate how many columns to shift as you drag the formula to the right.
Can I use this method if my sheet names contain text instead of just years?
Yes. If your sheets have prefixes, such as 'Year_2008', you can simply concatenate the prefix inside your formula string. For example, change "'" to "'Year_" in your INDIRECT formula so it matches the exact sheet name structure.
Why does the INDIRECT formula result in a #REF! error?
A #REF! error typically occurs if the sheet names generated by the formula do not exactly match the actual sheet names in the workbook, or if one of the sheets in the calculated year range is missing or deleted.
