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

 

[Nimrod]

Does this help ?

How to copy a formula from one sheet to another and keep the formula pointing to the first sheet.
Description: Copying formulas between sheets in Excel.
Last updated: November 25, 2001, Expires on May 26, 2002
Email this article to a friend


Well there are two ways. The first is easiest but not as flexible.

Highlight the cell/s on first sheet
Click on [Edit] menu and choose [Copy]
Click on the sheet you want to copy formula to.
Place cursor where you want to paste formula to.
Click on [Edit] and choose [Paste Special…]
Click on [Paste Link] button.
This will work as long as you want formula in second cell to change when you make changes the first cell. It is not flexible, but it works.

The second way to do it is a little more involved.

Highlight the cell/s on first sheet
Click on [Edit] menu and choose [Copy]
Click on the sheet you want to copy formula to.
Place cursor where you want to paste formula to.
Click on [Edit] and choose [Paste]
Click on cell now to edit formula
Add Sheetname! Just inside the opening brackets. (i.e. =SUM(A3:B3)
To =SUM(Sheet1!A3:B3))

While this requires a little more work, it is more flexible and far less time consuming then recreating all the formulas over again. To cut down on time you can copy and paste the sheet name into the new formulas.

source
https://iats.missouri.edu/servlets/knowledgebase/article/18101

 

[DBA]

Hi Henrik,

Don't copy the cell but highlight and copy the formula from the formula bar. (Ctrl-C is easy to use for Copy).

Go to the other workbook and paste to the formula bar (Ctrl-V)

Regards,

DBA

 

[Russell]

You could copy all the formulas you want over then go to Edit -> Links a change the source file to your current sheet.

Russell

 

[Qroozn]

you can always use the replace afterwards eg.
replace "[Energi Fyn_2 - test.xls]" with ""

 

[copleyr]

Is there a way to do this in Excel 2007?

 

[gexer]

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!

 

[DealPen]

Gexer has it right - I just did it. Nice job. This is an elegant solution and much easier than copying individual formulas. Changing the workbook source to the destination can quickly fix all the links in the worksheet.

 

[badandy400]

You could copy all the formulas you want over then go to Edit -> Links a change the source file to your current sheet.

Russell

Just wanted to give credit where it is due. Funny how someone gives a great answer, thanks Russell, and someone comes many years later and gives the same answer a post or two later and everyone praises that person. Haha. So just wanted to thank Russell for coming up with a great suggestion to an issue I was having. Certainly much easier than copy/paste/edit paste method.

 

[BenElliott]

You could copy all the formulas you want over then go to Edit -> Links a change the source file to your current sheet.

Russell

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