Forum Discussion
Copying formulas between spreadsheets without referencing the original spreadsheet
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.
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