'%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 Copy Excel Formulas Referencing the Previous Worksheet
Question details
The user needs to copy monthly Excel formulas into a new worksheet so that they automatically reference data from the previous month's worksheet, avoiding the need to manually edit sheet names in every formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating monthly tracking reports where each new month requires formulas to pull baseline data or compare values from the immediately preceding month's sheet.
- Observed behavior
- When copying formulas from one sheet to another, Excel does not automatically update the sheet name references (e.g., a formula referencing 'JAN' will still reference 'JAN' when copied to the 'MAR' sheet).
Verify that your monthly worksheets follow a consistent naming convention (such as JAN, FEB, MAR) and that the referenced data is located in the exact same cell addresses across all sheets.
Use the INDIRECT Function for Dynamic Sheet Referencing
Generate worksheet names dynamically by referencing a helper cell that contains the name of the previous month.
The INDIRECT function converts a text string into a valid cell reference. By combining text strings and cell values, you can change the referenced sheet name simply by updating a single cell in your current worksheet, rather than rewriting the formula.
In your new worksheet (e.g., the MAR sheet), type the name of the previous month's sheet (e.g., FEB) into an unused cell, such as A1.
Click on the cell where you want to perform the calculation or data pull.
Enter the formula using INDIRECT to build the reference. Type: =INDIRECT("'"&A1&"'!$D$31"). This tells Excel to look for cell D31 inside the sheet named in A1.
If you are using IF statements or other logic, nest the INDIRECT function inside it. For example: =IF(INDIRECT("'"&A1&"'!$D$31")>0, INDIRECT("'"&A1&"'!$D$31"), -INDIRECT("'"&A1&"'!$D$31")).
Press Enter to apply the formula. Whenever you copy this sheet for a new month, you only need to change the text in cell A1 to update all dependent formulas instantly.
Use Find and Replace to Update Sheet Names
A quick manual method to update static formulas in bulk after pasting them into a new monthly worksheet.
Easily Manage Multi-Sheet Data with WPS Office
WPS Spreadsheet makes it simple to manage complex, multi-sheet workbooks. You can seamlessly use advanced functions like INDIRECT and cross-sheet references to build automated monthly reports with ease.
- 1. Open your workbook: Launch WPS Spreadsheet and open your monthly tracking workbook.
- 2. Input the reference text: Type the target sheet name in a helper cell, such as A1, on your new monthly sheet.
- 3. Apply the INDIRECT function: Use the =INDIRECT("'"&A1&"'!$D$31") formula syntax to reference the previous sheet dynamically.
- 4. Calculate instantly: Press Enter to instantly fetch and calculate your data across worksheets.
Frequently Asked Questions
Why do I get a #REF! error when using the INDIRECT function?
A #REF! error typically occurs if the sheet name you are referencing is spelled incorrectly, contains spaces but isn't enclosed in single quotation marks within the formula string, or if the referenced worksheet has been deleted or renamed.
How can I copy the exact formula without changing cell references?
To copy a formula exactly as it is without Excel adjusting the relative cell references, click into the Formula Bar, highlight the text, press Ctrl+C, press Esc to exit the cell, and then paste the text directly into the target cell. Alternatively, use absolute references (like $A$1) before copying.
Can Excel automatically get the previous sheet's name without typing it in a cell?
Excel does not have a built-in function to dynamically grab the 'previous' sheet purely based on its physical tab order. To achieve this automatically without a helper cell, you would need to write and implement a custom VBA macro to determine the sheet index and fetch its name.
