Fix Excel Summary Links Pointing to a Previous Workbook
Question details
Update a copied summary worksheet so its formulas reference the current week's purchase-order sheets instead of the previous workbook.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A summary worksheet is copied each week into a workbook containing purchase-order sheets with unchanged tab names.
- Observed behavior
- The copied summary formulas continue to link to purchase-order sheets in the previous workbook.
Open the current workbook and check that its purchase-order tabs have the expected names. Select an affected summary cell and inspect the formula bar to identify the old workbook reference.
Change the outdated workbook link in Excel
Use Excel's workbook link controls to redirect an external source to the current workbook.
Open the copied workbook in Excel and select Data > Edit Links. In newer versions, look for Data > Workbook Links instead.
In the links list, select the previous week's workbook and choose Change Source.
Browse to the current workbook and select it as the new source. If Excel does not accept the current workbook as its own source, use the internal-reference solution below.
Return to the summary sheet, select affected cells, and inspect the formula bar to confirm they refer to the intended purchase-order sheets.

Replace external formulas with internal sheet references
Use this method when the purchase-order sheets are in the same workbook as the summary.
Update summary sheet references in WPS Spreadsheet
Open the weekly workbook in WPS Spreadsheet and edit summary formulas to reference purchase-order tabs in the same workbook.
- 1. Open the workbook: Launch WPS Office, choose Open, and select the current weekly workbook.
- 2. Inspect a summary formula: Open the summary tab, select an affected cell, and check the formula bar for the previous workbook name.
- 3. Use an internal reference: Edit the formula bar to reference the current workbook's sheet, such as ='Purchase Orders'!B2, and press Enter.
- 4. Verify the summary: Update the remaining affected formulas and compare their results with the values on the purchase-order tabs.

Frequently Asked Questions
Why do copied Excel formulas still reference last week's workbook?
When a worksheet is copied, Excel may preserve references to sheets in the original workbook as external links. Select a summary cell and check the formula bar for the old workbook name.
Where can I change an external workbook source in Excel?
Open the affected workbook and choose Data > Edit Links, or Data > Workbook Links in newer versions. Select the old source and choose Change Source.
How do I reference a sheet in the same workbook?
Enter a formula such as ='Purchase Orders'!B2 in the summary cell. Replace Purchase Orders and B2 with the actual tab name and cell you need.
What if Excel will not let me select the current workbook as the source?
Edit the affected formulas in the formula bar so they use internal sheet references instead of the external workbook path.




