Create Relative Worksheet References When Copying Excel Sheets
Question details
Create a formula that can reference the preceding worksheet after an Excel sheet is copied.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains copied worksheets that should each retrieve a value from the preceding sheet.
- Observed behavior
- A formula on a copied sheet may still refer to the original worksheet rather than the preceding sheet.
Check the exact name of the worksheet you want to reference and the address of the source cell. The example below uses cell A1 on a sheet named Period1.
Use INDIRECT with a Previous Sheet Name
Use a cell to specify the source sheet when copied worksheets need to reference different sheets.
INDIRECT builds a reference from text. It does not automatically identify the preceding sheet, so you must update the sheet name on each copy or use a naming-based formula.
On the worksheet containing the formula, enter Period1 in cell B1. Replace it with the exact name of the sheet you want to reference.
In the destination cell, enter =INDIRECT("'"&SUBSTITUTE(B1,"'","''")&"'!A1"). This retrieves cell A1 from the worksheet named in B1.
Right-click the worksheet tab, select Move or Copy, check Create a copy, and select OK.
On the copied worksheet, change B1 to the name of the sheet it should reference. For example, enter Period2 if the copy should retrieve A1 from Period2.

Update a Direct Sheet Reference After Copying
Use this approach when you only have a few copied sheets to adjust.
Build Worksheet References in WPS Spreadsheet
Use an INDIRECT formula in WPS Spreadsheet to retrieve a value from a sheet named in a cell, then update that name when you copy the worksheet.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Enter the sheet name: On the destination worksheet, enter the exact source sheet name in cell B1.
- 3. Add the reference: In the destination cell, enter =INDIRECT("'"&SUBSTITUTE(B1,"'","''")&"'!A1") to retrieve A1 from the sheet named in B1.
- 4. Update each copy: After copying the worksheet using its sheet tab, change B1 on the copy to the sheet name it should reference.

Frequently Asked Questions
Does Excel automatically reference the previous sheet when I copy a worksheet?
No. Excel may retain the original sheet reference. Update the formula or use a cell containing the desired sheet name with INDIRECT.
Can I use a naming pattern instead of typing the previous sheet name?
Yes. If sheets are named Period1, Period2, and so on, put the current period number in B1 and use =INDIRECT("'Period"&(B1-1)&"'!A1"). Update B1 on each copied sheet; the formula then points to the preceding numbered sheet.
What if a worksheet name contains spaces or apostrophes?
The formula =INDIRECT("'"&SUBSTITUTE(B1,"'","''")&"'!A1") handles both spaces and apostrophes in the sheet name entered in B1.
Why does my INDIRECT formula return a reference error?
Check that the sheet name in B1 exactly matches an existing worksheet tab and that the cell address in the formula is valid. INDIRECT references to external workbooks also require the source workbook to be open in Excel.




