Function Problems

How to Automatically Update Excel Formulas to Reference a New Worksheet

Olivia Miller Oct 22, 2026 868 views

Question details

The user needs to automate updating formula references, such as SUM or SUMIF, to pull data from a newly created weekly worksheet without manually editing each formula.

How to Automatically Update Excel Formulas to Reference a New Worksheet

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating a new worksheet every week and needing a master calculation sheet to pull data from the newest sheet dynamically.
Observed behavior: Currently, formulas must be manually edited each week to point to the newly created worksheet name. The goal is to make this reference update automatically.

Before you start

Ensure all your newly created weekly worksheets follow a consistent naming convention and that the data layout and ranges remain identical across sheets to prevent formula errors.

Solution 1Recommended

Use the INDIRECT Function to Build Dynamic References

Utilize the INDIRECT function to dynamically build cell references from text strings, allowing you to update a single cell with the new sheet name to update all linked formulas.

The INDIRECT function evaluates a text string as a valid cell reference. By storing your target sheet name in a dedicated cell, INDIRECT combines it with your specified range so that all formulas update automatically when the sheet name cell is changed.

Set up a reference cell

On your main calculation sheet, designate a cell (e.g., B1) to hold the name of your current weekly worksheet. Type the exact name of the target sheet into this cell.

Select the calculation cell

Click on the cell where you want to calculate your SUM, SUMIF, or other formulas.

Enter the INDIRECT formula

Type your formula using INDIRECT to reference the sheet name in B1. For example, to sum range A1:A10 on the new sheet, enter: =SUM(INDIRECT("'"&B1&"'!A1:A10")).

Update the sheet name weekly

Press Enter to apply the formula. Whenever a new weekly sheet is created, simply change the text in cell B1 to the new sheet's name. The formulas will recalculate and pull data from the new sheet automatically.

Use the INDIRECT Function to Build Dynamic References

Syntax Tip for Sheet Names: The single quotes enclosed in double quotes ("'") in the formula are required if your worksheet names contain spaces. Using them ensures the formula won't break regardless of how you name your sheets.

WPS Spreadsheet Solution

Easily Manage Dynamic Formulas in WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced functions like INDIRECT, allowing you to seamlessly handle dynamic data referencing across multiple worksheets without missing a beat.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your weekly data.
2. Designate a control cell: Input your desired worksheet name into a master reference cell at the top of your sheet.
3. Apply dynamic formulas: Use the =INDIRECT() function inside your calculations to instantly link your data to the new sheet.

Fully compatible with Microsoft Excel formulas, including INDIRECT, SUM, and SUMIFLightweight architecture ensures fast calculation speeds even with volatile functionsFree to download and use for comprehensive data analysis and spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula returning a #REF! error?

This error typically occurs if the text in your reference cell does not perfectly match the actual worksheet name, if there are trailing spaces in the sheet name tab, or if you are trying to reference an external workbook that is currently closed.

Can I use INDIRECT nested inside other functions like VLOOKUP or SUMIF?

Yes, INDIRECT can be nested inside almost any function that requires a cell or range reference. For example, you can use =VLOOKUP(A2, INDIRECT("'"&B1&"'!A1:D100"), 2, FALSE) to dynamically look up values on different sheets.

Will using the INDIRECT function slow down my spreadsheet?

INDIRECT is considered a 'volatile' function, which means it recalculates every time any change is made anywhere in the workbook. While it works perfectly for standard files, using thousands of INDIRECT functions simultaneously might decrease spreadsheet performance.

Excel & Data Problems