Copying formulas without the link

[Henrik Hougaard]

When I copy a formula from one workbook to another I get a link to the original workbook i.e.

=(0,06*'[Energi Fyn_2 - test.xls]skema2_soliditet_balance'!$F$26)

Is there a way to copy the formula without the link to the workbook ([Energi Fyn_2 - test.xls]) but still keeping the reference to the sheet (skema2_soliditet_balance'!$F$26)?

 

[Khaled040]

It doesn't work. Does anyone have any ideas how I can move several xl2010 worksheets that are full of large/complex formulae into another workbook without xl putting references into those formulae to the old workbook?

Thanks,

Ben

Try gexer post#7, it worked fine with me

 

[cdav7]

Sorry if my english is bad!
excel 2007
If you want to copy sheet from one workbook to another without link beeing added to formula try this.
Go to original workbook select sheet that you want to copy then right click select move or copy. Then select destination workbook check create copy box then ok. After this go to destination workbook select sheet that you copied then go to Data - Connections - Edit links
In this menu go to change source , then browse for workbook that you are working in. Select, OK and link is going to disapear from list then OK and that is it.
I hope this is going to help.
Again sorry about my english!

Gexer has the solution - Thanks! This works well for copying an entire sheet in Excel 2007 to another workbook. Both the source and destination workbooks must be open in the same instance of Excel. You must right-click on the tab for the sheet you want to copy in order to see the "Move or Copy" function.

 

[kriminaal]

What worked for me. I was only requiring part of a worksheet to copy over.
Copy and paste as normal >
Go to Data > Edit links and link back to the same sheet that you just pasted to.
It removes the link back and keep the formulas.

 

[longwayne]

I had the same problem just recently. And found a down and dirty method I deleted the = out of the formula, then copied it as a word format, after pasting it in the new workbook cell I reentered the = at the beginning.

 

[Shiseiji]

And if you have many to do, use Find/Replace to remove the "=." Then, after pasting them as text, on the next row, use Concatenate to put the "=" back in then copy/paste as values.

Ron

 

[Peteral]

It doesn't work. Does anyone have any ideas how I can move several xl2010 worksheets that are full of large/complex formulae into another workbook without xl putting references into those formulae to the old workbook?

Thanks,

Ben

Ben, sorry for replying years later. I don't know if this works for whole worksheets, but for formula generally, if you copy and past from one workbook to the other it keeps reference to the original worksheet. To prevent this, after you have copied the formula. press "Esc" button, then paste into the new workbook.