Reference the Previous Year's Worksheet in an Excel Formula
Question details
Create a formula that subtracts the same cell on the previous year's worksheet from the current year's value.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains worksheets named by year, and each sheet needs to calculate a change from the preceding year.
- Observed behavior
- A calculated year cannot be used directly as a worksheet reference, and formulas copied across sheets may show the same result if the sheet reference is not evaluated as intended.
Save the workbook and confirm that each worksheet is named with a four-digit year, such as 2024 or 2025. Make sure the previous year's sheet exists and that C4 contains the value to compare.
Build the Previous-Year Reference from the Worksheet Name
Use this formula when each worksheet is named by year and you want the reference to update based on the current sheet.
CELL retrieves the saved workbook and worksheet name, while INDIRECT converts the calculated previous year into a worksheet reference. The A1 argument ties CELL to a cell on the formula's worksheet.
Select File > Save before entering the formula. CELL("filename",A1) needs a saved workbook to return the worksheet name.
On a year-named sheet, select a cell other than C4 and enter: =IFERROR($C$4-INDIRECT("'"&TEXT(VALUE(MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,255))-1,"0000")&"'!$C$4"),"").
Copy the formula into the corresponding cell on each year-named worksheet. Each sheet will subtract C4 on the preceding year's sheet from its own C4.
If sheets show the same value unexpectedly, verify that the formula contains CELL("filename",A1), select Formulas > Calculation Options > Automatic, and recalculate with F9.
Use a Year Cell to Build the Previous-Year Reference
Use this approach if you prefer to enter the current year in a cell rather than extract it from the worksheet name.
Reference the Previous Year's Sheet in WPS Spreadsheets
In WPS Spreadsheets, enter the current year in a cell and use INDIRECT to build a reference to the previous year's worksheet.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select a worksheet named with a four-digit year.
- 2. Enter the year: Type the worksheet's year as a number in B1, and confirm that the preceding year's worksheet exists.
- 3. Add the formula: Select a cell other than C4 and enter =IFERROR($C$4-INDIRECT("'"&TEXT($B$1-1,"0000")&"'!$C$4"),"").
- 4. Repeat and save: Enter the appropriate year and formula on the other worksheets, then select File > Save.
Frequently Asked Questions
Why does the formula return a blank?
IFERROR hides errors by returning an empty string. Check that the previous year's sheet exists, the current sheet name or B1 contains a valid year, and the workbook is saved if you use CELL("filename",A1).
Why do several worksheets show the same result?
Check that CELL includes a worksheet-local reference, such as CELL("filename",A1), rather than omitting the reference. Then select Formulas > Calculation Options > Automatic and press F9 to recalculate.
Can I put the subtraction formula in C4?
No. Because the formula refers to the current sheet's C4, placing it in C4 creates a circular reference. Enter it in another cell.
How do I return the previous year's value without subtracting it?
If B1 contains the current year, enter =INDIRECT("'"&TEXT($B$1-1,"0000")&"'!$C$4") in the cell where you want to display the previous year's C4.
