Forum Discussion

fjlnyg's avatar
fjlnyg
Copper Contributor
Aug 18, 2023

How to copy formulas without...

How can I copy and paste the formulas from an existing spreadsheet into a new spreadsheet so that the formulas in the new sheet do not refer back to the existing sheet.

5 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor

    fjlnyg 

     

    How can I copy and paste the formulas from an existing spreadsheet into a new spreadsheet so that the formulas in the new sheet do not refer back to the existing sheet.

     

    That depends, to some extent, on the formulas themselves, how the references in them are written. Do they, for example, include the names of tabs or sheets within themselves. A simple formula like =A1+B1 should be readily copied, but if there are more elaborate internal references, that could be causing the difficulty.

     

    One thing you could do is, before copying, put an apostrophe before the equals sign in the existing spreadsheet's formula

    '={existing formula}

    copy that to the new spreadsheet and then remove the apostrophe.

    • fjlnyg's avatar
      fjlnyg
      Copper Contributor

      mathetes 

      Thank you so much for responding.

      Just to be clear, the sheet is substantial. It has about fifteen tabs and there are dozens of cells with formulas in each tab.

       

      When I do a copy and paste of an entire tab from the existing sheet, call it SHEET1 into a new sheet, call it SHEET2, every formula in SHEET2 begins with the reference to SHEET1.

       

      For example, if the formula in a cell in SHEET1 is:

      ='Income'!BF7

       

      Then the formula for that cell in SHEET2 shows up as:

       

      ='[SHEET1.xlsx]Income'!BF7

       

      I want is the formula in SHEET2 to not have the [SHEET1.xlsx] component in it.

       

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor

        There's a nuance:

        1. Copy desired formula from the formula bar.
        2. Paste formula into formula bar of destination sheet

Resources