Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Friday, 05 January 2018
  6 Replies
  5.5K Visits
0
Votes
Undo
I am trying to just copy the formulas of one Excel workbook to another, without having them both linked.

I thought I could use Exact Copy, but tho' this does copy just the formula (without the parent workbook's address - and just as it appears in the original workbook), there is still linkages back to the original. If I break this link (as keeping it slows down both workbooks, and defeats the whole object). When I break the link, I loose the copied formulas.

So how do I keep the copied formulas without having the linkage please?
6 years ago
·
#1378
0
Votes
Undo
Hello,
Please try this function https://www.extendoffice.com/product/kutools-for-excel/excel-list-break-links.html
And then go to copy the formulas?
6 years ago
·
#1379
0
Votes
Undo
Thanks Jay, but I cannot get this fix to work either. Using the KuTools 'Find and break links' only does what happened previously - the copied formula (which can be either a direct copy-and-paste - which includes the address of the original spreadsheet, or when I copy the desired formula by highlighting it alone and copying this) just disappears from the new cell.

It may help if I show an example:

Direct copy-and-paste: =IFERROR(IF(ABS(CORREL('[CancerData _01Sep07Onwards -No CondFormatting.xlsm]NormData'!B$14:B$3802,'[CancerData _01Sep07Onwards -No CondFormatting.xlsm]NormData'!$MR$13:$MR$3801))>'[CancerData _01Sep07Onwards -No CondFormatting.xlsm]Raw Data'!$B$3816,SLOPE('[CancerData _01Sep07Onwards -No CondFormatting.xlsm]NormData'!B$14:B$3802,'[CancerData _01Sep07Onwards -No CondFormatting.xlsm]NormData'!$MR$13:$MR$3801),""),"")

But if I copy the formula of the original cell by highlighting it, I get this in the target cell (which is what I really want, but not to then disappear when I break the link, as the above does too): =IFERROR(IF(ABS(CORREL(NormData!B$14:B$3802,NormData!$MR$13:$MR$3801))>'Raw Data'!$B$3816,SLOPE(NormData!B$14:B$3802,NormData!$MR$13:$MR$3801),""),"")

The latter is the formula I wish to have in my new spreadsheet (and I have lots of them), but breaking the linkage results in that formula now disappearing.

I hope this is clear, and that you may have an alternative way of copying my original formulas into the new spreadsheet which enables it to remain when the linkage is broken.
6 years ago
·
#1380
0
Votes
Undo
Hello,
but breaking the linkage results in that formula now disappearing.
Do you only want the results of the formula after moving the formula?
If yes, please go to copy and paste the formula into new workbooks, and then go to use this function to convert the formula's result into value only.
This function: https://www.extendoffice.com/product/kutools-for-excel/excel-replace-formula-with-value-result.html
If you want to keep the formula and the formula's result after breaking links, we don't have function for such operation.
6 years ago
·
#1381
0
Votes
Undo
Thanks Jay for getting back to me.

It is the latter I wish - having generated a complex set of formulas in one spreadsheet, I want to just use the identical formulas in a different spreadsheet. I'll just have to think of another method.
6 years ago
·
#1383
0
Votes
Undo
Hi Jay,

Thanks for that attempt at a solution.

Alas, when I do as you suggest, I still find the formula in the new spreadsheet has the link to the original one. So that isn't what I need.

Also, it would be one cell at a time, rather than a range; and so would be laborious. I may as well do this manually then.

However, I appreciate you working on my problem.

Kind regards, Ian
  • Page :
  • 1
There are no replies made for this post yet.