How to Automatically Adjust Cell References Across Sheets in WPS Spreadsheets
Question details
The user needs to create worksheet references that update automatically when formulas are copied, including methods compatible with Excel for the web.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a summary or consolidation sheet where formulas must pull values from other worksheets and adjust correctly when copied down or across.
- Observed behavior
- Manually editing each cross-sheet reference is repetitive and error-prone; the user wants relative and dynamic reference methods that update automatically.
Open the workbook in WPS Spreadsheets and confirm the exact sheet names you need to reference. If a sheet name contains spaces or special characters, enclose it in single quotation marks, for example 'Technical Sheet'!C8.
Use a standard cross-sheet reference that adjusts automatically when copied
Best for simple summary rows or columns where the source cell follows the same relative position and you want Excel-compatible behavior.
WPS Spreadsheets uses the same cross-sheet reference syntax as Microsoft Excel. When you copy a formula such as =Sheet1!C8, the row or column reference changes relative to the destination cell.
Click the cell where the formula result should appear, such as B2 on the summary sheet.
Type =Sheet1!C8 directly in the formula bar, or type =, click the source sheet tab, and click the source cell.
Press Enter, then drag the fill handle from the bottom-right corner of the selected cell. WPS Spreadsheets automatically updates the row or column reference.
If the sheet name contains spaces, enclose it in single quotation marks: ='Technical Sheet'!C8.
Create dynamic references with INDIRECT and ADDRESS when the target position changes
Use this when the source cell location must be calculated from the formula position, or when the destination and source layouts do not match.
Create Auto-Adjusting Cell References Across Sheets with WPS Spreadsheets
Use WPS Spreadsheets to create Excel-compatible cross-sheet references that update automatically when copied. Apply standard relative references or dynamic INDIRECT and ADDRESS formulas without changing your existing Excel workflow.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, then open the workbook that contains the source sheets and summary sheet.
- 2. Enter a standard cross-sheet reference: Select the destination cell, type =Sheet1!C8 in the formula bar, and press Enter. Use 'Sheet Name'!C8 for names with spaces.
- 3. Copy the formula to auto-adjust references: Drag the fill handle from the bottom-right corner of the selected cell down or across. WPS Spreadsheets updates the row or column reference automatically.
- 4. Switch to a dynamic reference if needed: For a reference generated from the formula position, enter =INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()+1,4)) and adjust the row/column offsets to your layout.
- 5. Check the results on the summary sheet: Review the pulled values and ensure the sheet name and cell target match the source data before copying the formula to the remaining cells.
Frequently Asked Questions
How do I make a cross-sheet reference update automatically when I copy a formula down?
Use a relative reference such as =Sheet1!C8. Select the cell, enter the formula in the formula bar, press Enter, then drag the fill handle down; the row reference updates automatically.
What is the syntax for a sheet name that contains spaces?
Enclose the sheet name in single quotation marks, for example ='Technical Sheet'!C8. The same applies inside INDIRECT when building a dynamic reference.
Can I use INDIRECT and ADDRESS in Excel for the web and WPS Spreadsheets?
Yes. Both WPS Spreadsheets and Excel for the web support INDIRECT and ADDRESS, so a formula like =INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()+1,4)) works in both.
Why would I use INDIRECT instead of a normal cross-sheet reference?
Use INDIRECT when you need the referenced cell to be calculated from the current formula position or from another cell value, rather than following a fixed relative copy pattern.
How do I keep the sheet name or column letter from changing when copying?
Use absolute references for the parts you want to fix, such as =Sheet1!$C$8 for a fixed column and row. For dynamic references, fix the text string in INDIRECT and adjust only the ROW or COLUMN values.
