Forum Discussion

bob_sipes's avatar
bob_sipes
Copper Contributor
Jun 25, 2020

Copying formulas between spreadsheets without referencing the original spreadsheet

how do i copy a formula from 1 spreadsheet to another, and keep the formulas from referencing the original spreadsheet? I want the formulas to work in my new spreadsheet, instead of referencing the spreadsheet i copied them from.

 

when i copy =SUM(Jan!U44) it turns into =SUM('[MORSUM 2017.xls]Jan'!U44)

 

excel is adding the "morsum2017.xls" which is the source file name.

  • bob_sipes 
    If I understood well your question, to resolve that, I usualy go to Data / Queries & Connections / Edit Links => Select the original spreadsheet ([MORSUM 2017.xls], on your exemple) => hit "Change Source" bottom => select the new spreadsheet.

    Usualy It works.

     

    But you must be carefully, in one case: If you have another cells in the same spreadsheet, with formulas (that you don't want to change) with references to the original spreadsheet, you will have a problem, because the formulas also going to be changed.

  • bob_sipes 
    If I understood well your question, to resolve that, I usualy go to Data / Queries & Connections / Edit Links => Select the original spreadsheet ([MORSUM 2017.xls], on your exemple) => hit "Change Source" bottom => select the new spreadsheet.

    Usualy It works.

     

    But you must be carefully, in one case: If you have another cells in the same spreadsheet, with formulas (that you don't want to change) with references to the original spreadsheet, you will have a problem, because the formulas also going to be changed.

      • inCentea-Miguel_Lopes's avatar
        inCentea-Miguel_Lopes
        Copper Contributor
        I was very happy to be able to help you.
        Curiously, it was also a good experience for me, because it was my second post on the Microsoft Tech Community.
        Probably, on the day, you will be the one to help me;)
    • axsimulate's avatar
      axsimulate
      Copper Contributor

      inCentea-Miguel_Lopes 

      Didn't work for me. When I go into Queries & Connections, there are none, yet it still wants to reference the original file. Very frustrating! I too don't want to copy/paste dozens of unique formulas one at a time from each cell. It would take all day. The only way I found so far that works 100% is to take several hours clicking on each cell one at a time and manually remove the references. Annoying a hell and a tremendous waste of time.

      • n8jones's avatar
        n8jones
        Copper Contributor
        Same here. The best solution I have found is the "Find and Replace" feature. You can select "find all"(insert original sheet name) then replace all (current sheet name).
    • diegocota's avatar
      diegocota
      Copper Contributor

      inCentea-Miguel_Lopes 

       

      What works for me is to go to Formulas tab --> Show Formulas, then copy the cells needed and paste into Notepad, then copy again and paste to the desired workboook. There will be a warning saying if you want to paste Active Content, click No. The pasted formulas will not have any reference to the old workbook. This method works for single or multiple cells at once, in a single row or multiple rows, and adjacent or non-adjacent formulas

  • wumolad's avatar
    wumolad
    Steel Contributor
    bob_sipes

    You can copy the formula from the formula bar instead of copying the cells.

    First, press F2 to edit the cell and copy the formula, then paste to the other worksheets.

    Cheers
    • mathetes's avatar
      mathetes
      Silver Contributor

      bob_sipes 

       

      Not sure exactly what your situation is, but in reading your last plea--I was hoping not to copy and edit all 350 formulas--it occurred to me that you might not be aware that you can simply copy the entire sheet either within the same workbook or to an entirely new workbook.

       

      Just right click on the tab at the bottom and select "Move or Copy" and proceed to either, uh, move or copy, based on how you fill out the dialog box. That will keep the original formulas all with their internal reference points. It's a way (in effect) to have one sheet serve as a template for others designed to do the same thing.

       
    • wumolad's avatar
      wumolad
      Steel Contributor
      bob_sipes

      I wasn't aware you were copying 350 formula, hence my earlier submission.

      So the fastest solution will be to copy the formula the way you earlier did and then replace all the instances of '[MORSUM 2017.xls] with nothing using find and replace feature.

      I am sure copying the sheet from the other workbook may not work if the formula on the sheet is still linked to other sheets in the old workbook as the filename will still be showing.

      Cheers

Resources