Copy Formulas Across Multiple Sheets and Increment References in WPS Spreadsheets
Question details
The user needs to copy formulas across multiple worksheets in WPS Spreadsheets and have cell references increment relative to each destination sheet instead of continuing to point to the original sheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing multiple daily or departmental sheets that share the same layout and formula structure.
- Observed behavior
- Copied worksheets keep referencing the original sheet because formulas contain absolute references, explicit sheet names, or otherwise do not adjust during copying.
Before copying, check whether your formulas use relative references such as A1 or absolute references such as $A$1. Also confirm that the destination sheets have the same layout as the source sheet so relative references continue to point to the correct cells.
Use relative references before copying formulas
This is the fastest fix when each sheet keeps the same layout and you want reference positions to adjust automatically after pasting.
Excel and WPS Spreadsheets adjust relative references based on where you paste the formula. Absolute references, written with $, stay locked and are often the reason copied sheets still point back to the original sheet.
Open the workbook in WPS Spreadsheets and select the cell that contains the formula you want to copy.
Click inside the formula bar and press F4 repeatedly until references appear without $ signs, for example A1 instead of $A$1.
Press Ctrl+C or click Home > Copy to copy the selected formula cell.
Open the destination worksheet, select the target cell, and press Ctrl+V or use Home > Paste. The references now increment relative to the paste location.
Consolidate data into one sheet and summarize with a PivotTable
Use this approach when you manage many daily or departmental sheets that have the same layout and mainly need reports by date or group.
Build flexible references with INDIRECT when sheet names come from cells
Use this method when a formula must construct a reference from a sheet name stored in another cell, not when simple relative copying is enough.
Copy and Increment Formulas Across Sheets in WPS Spreadsheets
WPS Spreadsheets lets you fix copied worksheet reference issues directly by using relative references before copying, or by consolidating multiple sheets into one database sheet and summarizing with a PivotTable.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the source formulas and multiple sheets.
- 2. Convert fixed references to relative references: Select the formula cells, click in the formula bar, and press F4 until references show without $, such as A1 instead of $A$1.
- 3. Copy and paste formulas to another sheet: Press Ctrl+C, open the destination sheet, select the first target cell, and press Ctrl+V so references increment correctly.
- 4. Verify the copied references: Click the pasted cells and check the formula bar to confirm they now reference the current sheet's cells, not the original sheet.
- 5. Summarize many sheets if needed: If you manage many identical sheets, move the raw data to one sheet and use Insert > PivotTable to summarize by date or department.
Frequently Asked Questions
Why does my copied worksheet still refer to the original sheet?
Usually the formula contains absolute references such as $A$1 or an explicit original sheet name. Select the formula cell, click in the formula bar, and press F4 until the reference shows as relative, such as A1, before copying.
How do I toggle between absolute and relative references in WPS Spreadsheets?
Select the cell and enter edit mode, then press F4 repeatedly to cycle through reference types: A1, $A$1, A$1, and $A1.
How can I make a formula use a sheet name typed in another cell?
Use INDIRECT. For example, if A1 contains the sheet name, enter =INDIRECT("'"&A1&"'!B2") to reference cell B2 from that sheet. Use it sparingly because INDIRECT is volatile.
Instead of copying many sheets, how can I summarize daily or departmental data?
Place all records in one database sheet with columns such as Date and Department, then click inside the data and go to Insert > PivotTable to summarize by date or department.
Can WPS Spreadsheets open and save Excel files with these formulas?
Yes. WPS Spreadsheets supports .xlsx, .xls, and .csv files and preserves common formula behaviors, including relative references and INDIRECT.
