How to Pull an End-of-Month Amount into a New Worksheet in WPS Office Spreadsheets
Question details
The user wants an end-of-month balance from a source worksheet to appear automatically in the following month’s worksheet and update whenever the source value changes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Monthly reporting or balance carry-over where the previous month’s ending amount must appear in a new worksheet.
- Observed behavior
- The destination cell should display the source amount and refresh automatically when the source value is edited.
Confirm the exact source worksheet name and the cell address of the end-of-month amount before entering the formula. If the sheet name contains spaces, wrap it in apostrophes, such as ='January 2024'!A1.
Create a Direct Cross-Sheet Reference to the End-of-Month Amount
Use this method when the end-of-month balance is stored in a known cell and the new worksheet should update automatically whenever the source amount changes.
A cross-sheet reference links the destination cell directly to the source cell, so there is no need to copy and paste values every month. The formula follows the pattern =SheetName!CellReference or ='Sheet Name'!CellReference for sheet names that include spaces.
Open the new month’s worksheet in WPS Office Spreadsheets and click the cell where the end-of-month amount should appear.
Type = in the selected cell. You can continue typing the source worksheet name and cell address manually, or click the source worksheet tab and then click the source balance cell to insert the reference.
For example, enter =OriginalWorksheetName!A1. If the source sheet is named January 2024, enter ='January 2024'!A1 and press Enter.
Confirm that the destination cell shows the current source balance. When the source amount changes, the destination value updates automatically.
Reference an End-of-Month Amount Across WPS Spreadsheets
WPS Office Spreadsheets can link the destination cell directly to the source balance with a simple cross-sheet formula. The referenced value remains live, so your new month’s worksheet updates automatically without manual copy and paste.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains both the source month sheet and the new month’s destination sheet.
- 2. Select the target cell: Go to the new month’s worksheet and click the cell where the end-of-month amount should be displayed.
- 3. Enter a cross-sheet reference: Type =, then either click the source worksheet tab and the balance cell or type the formula manually, such as ='January 2024'!A1.
- 4. Press Enter and verify: Press Enter to complete the formula and verify that the value appears from the source worksheet.
- 5. Save the workbook: Save the file in WPS Office Spreadsheets or as an Excel-compatible .xlsx file to keep the link intact.
Frequently Asked Questions
What formula pulls a value from another worksheet in WPS Spreadsheets?
Use a direct reference such as =SheetName!CellReference. In WPS Office Spreadsheets, type = in the destination cell, click the source sheet tab, and then click the source cell to insert the reference automatically.
How do I reference a source sheet with spaces in its name?
Wrap the sheet name in apostrophes. For example, use ='January 2024'!A1. Type the apostrophe, the sheet name, a closing apostrophe, then ! and the cell reference.
Will the amount update automatically when the source cell changes?
Yes. A cross-sheet cell reference is live. When the source balance in the original worksheet is edited, the destination cell recalculates and displays the new value.
Can I pull a balance into a new workbook instead of a new worksheet?
Yes, but the reference must include the workbook name, such as =[WorkbookName.xlsx]SheetName!A1. Keep the source workbook open or update links when prompted if the workbook is closed.
