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.
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.
- 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).
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.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)
