Forum Discussion

bob_sipes's avatar
bob_sipes
Copper Contributor
Jun 25, 2020
Solved

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 s...
Formulas and Functions
  • inCentea-Miguel_Lopes's avatar
    inCentea-Miguel_Lopes
    Jun 26, 2020

    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
May 10, 2024

If I understood well your question, to resolve that, I added to n8jones post, a small detail, if it doesn't work:

Using the initial example: SUM(Jan!U44) it becomes =SUM('[MORSUM 2017.xls]Jan'!U44)
try on the "Find and Replace" function:
FIND WHAT: '[MORSUM 2017.xls]Jan'

REPLACE WITH: Jan
Select REPLACE ALL

NOTE: when you have a external referencing, Excel formula needs '', that are not necessary on internal referencing. 

inCentea-Miguel_Lopes's avatar
inCentea-Miguel_Lopes
Copper Contributor
May 10, 2024

I wanted to clarify one point: when you work on the new worksheet, on the same sheet where you have the cells, the Replace function is different:
FIND WHAT: '[MORSUM 2017.xls]Jan'!
REPLACE WITH:

NOTE: on FIND WHAT you need to added !
and on REPLACE WHITH you don't put anything on (maintain blank)

 

Resources