0

I have a complex spreadsheet and need to copy some of the tabs to another spreadsheet file. Formulas no longer work when I am done copying. How can I fix them?

1

It is easy to copy sheets manually from a spreadsheet to another, but there are often issues with cross-sheet references that tend to get broken. This problem can usually be minimized by copying the sheets in their "dependency order." In other words, you should identify the sheet that has the least formulas that refer to cells in other sheets, and copy it first. This way, sheets that are copied later will see the first sheet at its new location and most of the dependencies get resolved automatically. Follow these steps:

  1. Take the two spreadsheets that you want to merge. Establish one of them as the target spreadsheet that will in the end host sheets from the two spreadsheet, and establish the other one as the source spreadsheet.

  2. In the target spreadsheet, rename the sheets so that all the sheet names differ from those in the source spreadsheet.

  3. In the source spreadsheet, find the sheet that depends the least on the other sheets, and copy it to the target spreadsheet by clicking the Down arrow ▼ on the sheet tab at the bottom of the browser window and choosing Copy to > Existing spreadsheet.

  4. Rename the copied sheet in the target spreadsheet by removing the prefix "Copy of " or similar. The resulting name should be exactly the same as the name of the sheet in the source spreadsheet.

  5. In the source spreadsheet, find the sheet that depends the second least on the other remaining sheets. This sheet may depend on the sheet you copied first, but preferably should not depend on other remaining sheets. Copy this sheet to the target spreadsheet.

  6. Rename the copied sheet in the target spreadsheet by removing the prefix "Copy of " or similar. The resulting name should be exactly the same as the name of the sheet in the source spreadsheet.

  7. Continue in this fashion until you have copied all the sheets.

Finally, in the target spreadsheet, locate any formula cells with broken range references, and fix them using the intact formulas in the source spreadsheet as guide. Use Edit > Find and Replace like this:

  • Find: ^=[^]*#REF!
  • Search: All sheets
  • [x] Search using regular expressions
  • [x] Also search in formulas

Click Find to discover the broken formula cells. Find the original formulas at the same location in the source spreadsheet and use them as guide to rewrite the formulas in the target spreadsheet.

If you have named ranges in the source spreadsheet, there is one more thing to do. Named ranges will usually be changed in the target spreadsheet to something like "Sheet1!NamedRange1" and may or may not continue working.

One way to deal with these named ranges is to open the Data > Named ranges sidebar and write down the names and ranges of them all, then delete all the named ranges, copy the sheets over, and recreate the named ranges on the target spreadsheet using your notes. The formulas that refer to the named ranges should start working once you have recreated the named ranges. You can refresh the formula cells that continue to show #NAME? by selecting the cell and pressing Enter Space Enter, or by using a find and replace routine like the one described above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.