Paste special function lacks possibility for copying formulas in '

[Guest]

When creating worksheets, you often need to copy a specific function from one
area to another in the same worksheet (or to another), without changing the
original formulas. This is not possible as a 'paste special funtion'.

When copying formulas in 'paste special' mode, the relative references in
the formula are still changed. In short, when copying the 'formula' =A25' to
another cell, the formula in the copy to cell should still be '=A25'.

Suggestions of changing the orginating formula from relative to absolute
reference (e.g. $A$25) before copying doesn't solve the problem, since the
originating cell MUST have a relative reference (A25 instead of $A$25).

Any suggestion or functions, that I have overlooked, - or is this a
functionality lacking of Excell??

 

[Guest]

Hi
you could copy the formula text directly from the formula bar and inserting
it directly in the formular bar of your target cell

 

[Guest]

Hi Frank,

Thanks for the input. I have done this so far, - but it is very tedious,
when you want to copy a block of cells with many different relative
references from one area to another.

Regards
Jan Kolbak

"Frank Kabel" skrev:

 

[Guest]

Hi
another idea:
- in your sorce range replace the equation sign (with 'Edit - Replace') with
something else. e.g. $$$
now copy this range to your target range and undo this replacement(e.g.
replace '$$$' with '=')

 

[Guest]

Hi again Frank,
A good idea, which could be used as a work-arround,- although this
replacement then have to be done 3 times; - one time on the source-cells and
then afterwards replacing back the equal signs on both the source- and
destination cells.
Nevertheless thank you for your suggestion.

I still cannot understand, why this isn't a standard functionality within
the 'Paste Special functionality' of Excel. I cannot be the only one lacking
this general facility.

This 'new' function could e.g. be called 'Paste formulas absolute', placed
directly under the 'Paste formulas' field. I asume, you agree on this need.
It should be communicated directly to Microsoft functionality team..

Regards
Jan


"Frank Kabel" skrev:

 

[Guest]

Hi Jan

(e-mail address removed)

and make sure to mention Excel in the subject line

 

[Gord Dibben]

Jan

Would you be interested in a Macro to copy formulas without changing
references?

Gord Dibben Excel MVP