Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel:

How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[WorkbookA.xlsx]!B!23 when copied over.

I want to maintain "relative" cell references instead of "absolute" cell references (i shall invent this terminology in Excel world if it does not yet exists).

Another possible alternative that I cannot get it to work is the option to paste cell "values" only. Excel treats "values" as calculated values rather than the actual formulas in the cell. If I choose paste formula, it still gives absolute references.

More About Why I Need This: I have a production xlsx in use for daily operations. We constantly need to make "upgrades" to this xlsx and so one person may create a copy and his changes there for a single sheet. Concurrently, another person may also be making changes to another sheet. Given that these sheets have no dependant cells on other sheets, like a summary report, it is desirable for us to just copy and merge the sheets back into the original xlsx. But the "absolute" referencing is giving a lot of trouble.

Solution 1:

Try using Ctrl + ~ to display the formulas. Then use Ctrl + A to select everything, copy it and then paste it into notepad.

Finally, copy it out of notepad and paste it into your other workbook.

Solution 2:

I've found it easier, in many cases, to do the following:

• copy the sheet to a new workbook
• activate the new sheet in the new workbook
• select all (Ctrl+A)
• do a find/replace on
  • find: [WorkbookA.xlsx]!
  • replace: <leave blank>
• replace all

Solution 3:

The unsigned answer right below this one is the one that worked for me, with a very slight variation.

1. Create and save a destination spreadsheet.

2. Use "move", "copy", or drag your page with the formulas into the new spreadsheet. This leaves the formulas on the new page pointing to the old worksheet. Then save the new spreadsheet in the same location as the old worksheet.

3. Then go to the Data Tab > click Edit Links. The option won't be active unless there are links in the page.

4. In the dialog that results, select the name of the source file and click "Change Source."

5. From the open-file dialog that appears next, select the name of the new spreadsheet.

Click Close and you're finished.

Solution 4:

Or simply do the following:

Convert this:

=database_feed!A1

to this:

=INDIRECT("database_feed!A1")

and no more changes to your references when you copy between worksheets.

If you don't have many sheets referenced, another alternative would be to use

=INDIRECT("'"&B1&"'!A1")

and enter the name of the reference sheet in cell B1. Now you only have one cell to update when copied to the new spreadsheet.

Solution 5:

Since 99% of the responses didn't even address the original question, here's the proper response.

1. Copy the sheets from the original file (Original.xlsx) to the new Excel file (New.xlsx) as you normally would. Generally, I right-click on the name and choose "Move or Copy...".

2. Save the second - newly created file (New.xlsx).

3. In the new file, under Data, click "Edit Links"

4. In the pop-up, choose "Change Source..."

5. Locate the file (New.xlsx) and click Open.

All references to the original (Original.xlsx) will be removed.

DONE!