How do I copy & paste formulas without them linking to the source

[Guest]

How do I copy & paste formulas without the formulas linking back to the
original source spreadsheet. The formulas should calculate only from
internal cells and tabs .

 

[Bill Kuunders]

Change the formula to text by inserting a ' in front.
Then copy /paste, and remove the '.

 

[Dave Peterson]

If you're doing one cell, you could copy from the formulabar, then paste into
the formulabar. You'll have to adjust the formula yourself.

If you're doing lots...
Select the range to copy
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Do your copy|paste

Then reverse it (in both worksheets/workbooks):

Select the range
edit|replace
what: $$$$$
with: =
replace all

Since you're copying text, you may have to change the ranges.

 

[Dave Peterson]

Using that apostrophe makes it very difficult to do edit|replace to get all the
"formulas" at one time.

 

[RagDyer]

But makes it very easy *if* they're in a column by simply opening and
closing Text To Columns.

 

[Dave Peterson]

But if there's numbers that look like text ('1234123412341234), it might not be
worth the hassle.

 

[RagDyer]

True, but just shows all the ways XL can skin that cat.

 

[Guest]

Worked like a charm.

Thanks

If you're doing one cell, you could copy from the formulabar, then paste into
the formulabar. You'll have to adjust the formula yourself.

If you're doing lots...
Select the range to copy
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Do your copy|paste

Then reverse it (in both worksheets/workbooks):

Select the range
edit|replace
what: $$$$$
with: =
replace all

Since you're copying text, you may have to change the ranges.

 

[David McRitchie]

If you paste to the formula bar rather than the cell you can copy one formula
without adjustment of addresses.

If you would like a macro to do this see
Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm
--