Copy formula without link

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
Is it posssible to paste a formula from one workbook to another without creating a link to the source file?

I'd like to copy a whole tab worth of formulas but I do not want to linkt o the file they are coming from.

Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I'm not to sure that it is....

but once copied over, you can do a Find/Replace All and replace the linked workbook name with nothing.
 

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
Thanks

Yes I'm aware of that but I wodered if there is a cleaner method.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
What I usually do is do a Find/Replace on the source sheet Finding "=" (without the quotes) and Replacing With "'=" (w/o double but with single quote) which turns all the formulas into text. Then copy and paste, and reverse the Find/Replace to turn back into formulas.

Richard
 

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887

ADVERTISEMENT

Thats an interesting approach, I'lll use that.

But for my original question, there is no 1 step operation to do this it appears?
 

KentKHI

Active Member
Joined
Oct 1, 2004
Messages
492
Bringing this back

I tried this, but I can't get it to replace the '= with an = because it says it can not find the '=.

The cell doesn't show the ' and I have seached for != as one other thread said I should do.

Please help, as I am stuck and can't back up now :cry:

Thanks
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I must admit I had problems with it too - so much so I now replace + with #= which Excel seems to have no problem recognising the replacement as text.

Anyway, that doesn't help you now. Try selecting youyr formulas and going Format>Cells and changing to Text format and now replacing '= with = You will then have to convert the cells back to eg General format and then do a Find/Replace with = for = to convert back to actual formuals. Hopefully this will work - post back if not and we'll try something else.

Hope to help!
 

Forum statistics

Threads
1,141,589
Messages
5,707,267
Members
421,499
Latest member
Dpbj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top