Function Problems

How to Create Excel Worksheets That Reference the Previous Sheet

Nimra Malik Oct 9, 2026 870 views

Question details

The user needs to create a monthly budget workbook where each newly copied worksheet automatically references totals from the immediately preceding worksheet without requiring manual formula updates.

How to Create Excel Worksheets That Reference the Previous Sheet

Product: Excel
Device & OS: not provided
Scenario: Managing a rolling workbook (like a monthly budget report) where data, such as a closing balance, needs to carry over to the new sheet as the opening balance.
Observed behavior: When standard worksheets are copied, the formula references to previous sheets remain static (e.g., pointing to Sheet1 permanently) instead of automatically shifting to the previous sheet.

Before you start

Before applying dynamic references, ensure your worksheets follow a consistent naming convention (such as 'Jan', 'Feb', 'Mar' or 'Sheet1', 'Sheet2') to make formula structuring predictable.

Solution 1Recommended

Use the INDIRECT Function for Dynamic References

The INDIRECT function allows you to construct a cell reference using text strings, which is useful when you want to reference a specific sheet name dynamically.

INDIRECT is a powerful tool for referencing other sheets. However, note that a text string inside an INDIRECT formula does not automatically change when a worksheet is copied. You will either need to update the text string manually upon copying or reference a cell containing the previous sheet's name.

Also, INDIRECT is a volatile function. This means it recalculates whenever any change is made to the workbook, which may reduce performance in very large files.

Set up a helper cell for the sheet name

In your current worksheet, designate a cell (for example, A1) and type the exact name of the previous worksheet you want to reference (e.g., 'Sheet1').

Enter the INDIRECT formula

Select the cell where you want the referenced total to appear. Enter the formula: =INDIRECT("'" & A1 & "'!B10")-(Payment1+Payment2). This fetches the value from cell B10 of the sheet named in A1.

Update the helper cell when copying

When you duplicate the worksheet for the next month, simply click on cell A1 and update it to the new previous sheet's name. The INDIRECT formula will instantly update its reference.

Use the INDIRECT Function for Dynamic References

Direct String Reference: If you prefer not to use a helper cell, you can write the formula as =INDIRECT("Sheet1!B10")-(Payment1+Payment2), but you will need to manually edit 'Sheet1' in the formula bar each time you copy the worksheet.

Solution 2

Use a VBA User-Defined Function (UDF)

Create a custom VBA script to automatically pull data from the worksheet located immediately to the left of the active sheet, bypassing the need for manual name updates.

Efficient Spreadsheet Solution

Easily Manage Rolling Budgets with WPS Spreadsheet

WPS Office provides a highly compatible and intuitive spreadsheet tool. You can seamlessly use advanced functions like INDIRECT or run VBA macros to automate your monthly workbook references with ease.

1. Open WPS Spreadsheet: Download and launch WPS Office, then open your existing monthly budget workbook.
2. Apply the Reference Formula: Select the target cell and type your standard formula, such as =INDIRECT("Sheet1!B10"). WPS processes this exactly as Excel does.
3. Copy the Worksheet: Right-click the sheet tab at the bottom, choose 'Move or Copy', check 'Create a copy', and easily duplicate your template for the new month.

100% compatibility with Microsoft Excel formulas, named ranges, and VBA macros.Lightweight application with fast calculation speeds, minimizing the performance impact of volatile functions like INDIRECT.Built-in name manager and structured reference support for complex budgets.Free to use with a familiar, easy-to-navigate tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard cell reference update when I copy the worksheet?

When you duplicate a worksheet, the spreadsheet software preserves the exact sheet references inside your formulas to maintain data integrity. If Sheet2 references 'Sheet1'!A1, creating a copy of Sheet2 will result in a Sheet3 that still points to 'Sheet1'!A1, requiring a manual update.

Does using the INDIRECT function slow down my workbook?

Yes, INDIRECT is known as a 'volatile' function. This means it recalculates every single time any change is made anywhere in the workbook. While this isn't noticeable in small budgets, it can cause performance lags in complex files with thousands of formulas.

Can named ranges help with linking to previous sheets?

Yes, using a structured workbook design with named ranges makes your formulas significantly easier to read and troubleshoot (e.g., =Jan_Total + New_Income). However, you must carefully manage the scope of those names (Workbook vs. Worksheet level) to ensure they pull from the correct month when copied.

Excel & Data Problems