Copy Excel worksheet and maintain relative cell reference in formulas

Question

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!

View more solutions

Comments

[Jake]

A single routine is nice as I am manually CTRL+H now, but all these do not fix the refences for controls e.g. buttons, dropdowns and such; and named ranges.

[fixer1234]

Can your expand your answer a little? It isn't clear how/where to drop on another tab while dragging. Thanks.

[Máté Juhász]

It's not clear how your answer solves the problem of the question. Why move will work in different way then copy would work?

[nixda]

I tried your proposed solution and it does not work. There is no difference between copying and moving entire sheets.

[Scott - Слава Україні]

@Jake: But you have said that your workbook has buttons, dropdowns and such; and named ranges. This technique won’t copy them (or mundane things like formatting) — how/why do you consider it acceptable?

[Douglas Gaskell]

Sadly excel seems to only intermittently work when it comes to finding sub-strings inside of formulas :/. It doesn't seem to be capable of finding anything with single quotes either.

[RogerB]

Good idea, works if you have a template sheet with formulas and you are copying this into a new workbook with VBA. I found that you need to keep the reference between quotes and add TRUE to keep A1 type references. eg. =INDIRECT("'Sheet1'!A1", TRUE)

[airstrike]

INDIRECT is a volatile formula so it will be recalculated every time (as opposed to only when it's edited) which can really slow down more complex workbooks, so use with caution

[Tony Pulokas]

On a large worksheet, the Edit Links/Change Source technique was painfully slow, but this was fast.

[sabre23t]

Should be higher in the list of answers

[Kar.ma]

It works, but there is no exclamation mark to be removed. I couldn't edit above question without editing at least 6 characters, so I'm just leaving this comment.

[Steev43230]

Does it create the sheet in the new workbook? Will it create the sheet in all workbooks in a folder? Tryna avoid doing that manually in 200+ workbooks.

[Steev43230]

Very nice. Now if it cold be automated...

[Steev43230]

Be nice to be able to apply this to all workbooks in a folder.