Forum Discussion
Copying formulas between spreadsheets without referencing the original spreadsheet
- 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.
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_sipesJun 26, 2020Copper Contributor
Brilliant, that did exactly what i wanted and needed. Thank you for sharing your time and knowledge.
- inCentea-Miguel_LopesJun 26, 2020Copper ContributorI 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;)
- axsimulateFeb 28, 2024Copper 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.- n8jonesMay 09, 2024Copper ContributorSame 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).
- inCentea-Miguel_LopesMay 10, 2024Copper Contributor
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.
- diegocotaOct 27, 2024Copper Contributor
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