How to Carry a Balance Forward from the Previous Excel Worksheet
Question details
The user needs to retrieve the ending balance from the immediately preceding monthly worksheet into the current month's worksheet without manually changing the formula for every single month.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a rolling 12-month financial template where each month's starting balance automatically pulls the previous month's ending balance.
- Observed behavior
- Using a direct sheet reference like =June!H30 works for one sheet, but when the template is copied for subsequent months, the formula statically references June instead of dynamically updating to the previous month.
Ensure all your monthly worksheets are organized in strict chronological order (e.g., January, February, March) from left to right, and that the ending balance cell is located in the exact same cell reference on every sheet.
Use a Custom VBA Function to Reference the Previous Sheet
Use a VBA macro to create a custom formula that automatically pulls data from the worksheet immediately to the left of the current one. This is the best method for reusable 12-month templates.
By creating a User Defined Function (UDF) in VBA, you can use a formula like =PrevSheet(H30) on any sheet. It will always calculate based on the sheet right before it, eliminating the need to manually type the previous month's name.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
Click on 'Insert' in the top menu bar, then select 'Module' to create a blank script window.
Paste a standard UDF script for referencing the previous sheet index (e.g., Application.Caller.Parent.Index - 1) into the module window.
Go to 'File' > 'Save As' and ensure you save your workbook as an Excel Macro-Enabled Workbook (*.xlsm).
Go back to your monthly worksheet, click the starting balance cell, and type your new custom formula (e.g., =PrevSheet(H30)) to pull the balance from the preceding tab.
Use a Direct Sheet Reference Formula
The most straightforward method is to manually reference the previous month's sheet. This is suitable for simple workbooks with only a few sheets where VBA is not permitted.
Easily Carry Balances Forward in WPS Spreadsheet
WPS Spreadsheet offers full compatibility with advanced Excel formulas and VBA macros, allowing you to seamlessly link your monthly balance sheets without repetitive manual updates.
- 1. Open Your Template: Launch WPS Spreadsheet and open your multi-month financial workbook.
- 2. Apply Direct References: For simple links, type '=' in the new month's starting balance cell, click the previous month's tab, and select the ending balance.
- 3. Enable Developer Tools: For advanced VBA automation, click on the 'Developer' tab to insert modules and paste your custom previous-sheet functions.
- 4. Automate the Workflow: Apply your custom formula across all 12 monthly tabs seamlessly, completely free of charge.
Frequently Asked Questions
Why does copying my monthly sheet keep the same formula reference?
When you duplicate a worksheet, the spreadsheet program retains the exact text of your formulas. If your formula was =June!H30, the new copied sheet will still point specifically to the 'June' sheet. To make it dynamic, you must use VBA or the INDIRECT function.
Can I use the INDIRECT function to carry a balance forward without VBA?
Yes. If you type the previous month's exact sheet name in a helper cell (for example, typing 'June' in cell A1), you can use the formula =INDIRECT("'" & A1 & "'!H30"). You will still need to manually update cell A1 each month, but the main balance formula can remain untouched.
Do macros work in WPS Office for carrying balances forward?
Yes, WPS Spreadsheet supports VBA macros. You can write or import a User Defined Function (UDF) to dynamically reference the previous sheet's index, and it will function exactly as it does in Microsoft Excel.
