Function Problems

Create Relative Worksheet References When Copying Excel Sheets

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Create a formula that can reference the preceding worksheet after an Excel sheet is copied.

Create Relative Worksheet References When Copying Excel Sheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the source sheet name

On the worksheet containing the formula, enter Period1 in cell B1. Replace it with the exact name of the sheet you want to reference.

2
Enter the formula

In the destination cell, enter =INDIRECT("'"&SUBSTITUTE(B1,"'","''")&"'!A1"). This retrieves cell A1 from the worksheet named in B1.

3
Copy the worksheet

Right-click the worksheet tab, select Move or Copy, check Create a copy, and select OK.

4
Update the source name

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.

Use INDIRECT with a Previous Sheet Name
Consider workbook size: INDIRECT is volatile and can slow recalculation when used extensively in large workbooks. Renaming a source sheet does not automatically update a sheet name stored as text in B1.
WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Enter the sheet name: On the destination worksheet, enter the exact source sheet name in cell B1.
  3. 3. Add the reference: In the destination cell, enter =INDIRECT("'"&SUBSTITUTE(B1,"'","''")&"'!A1") to retrieve A1 from the sheet named in B1.
  4. 4. Update each copy: After copying the worksheet using its sheet tab, change B1 on the copy to the sheet name it should reference.
Use a sheet name in a cell to control a worksheet referenceOpen and edit Microsoft Excel XLSX and XLS filesWork with familiar spreadsheet formulas and sheet tabsUse WPS Office for free
microsoft office alternative - wps office

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.