Forum Discussion
bob_sipes
Jun 25, 2020Copper Contributor
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...
- 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
Jun 25, 2020Copper Contributor
I was hoping not to copy and edit all 350 formulas.
wumolad
Jun 25, 2020Steel 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
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