copying formulas

[texbetty]

I'm a self-taught Excel user (semi-intermediate level), although I'm having a
"formula" problem:
When I copy the "value" of a cell from cell A (which contains a formula)….
And paste special to cell B (for value only)…. I don't want the contents in
cell A to change. I wish for them to stay as a formula. Can this be done?
My setting under TOOLS is for the calc = Automatic.

I use massive formulas on a month-to-month basis and have to constantly
double-check my "copied from formulas" to ensure they are edited back to a
formula.
texbetty

 

[Francois via OfficeKB.com]

I'm a self-taught Excel user (semi-intermediate level), although I'm having a
"formula" problem:
When I copy the "value" of a cell from cell A (which contains a formula)….
And paste special to cell B (for value only)…. I don't want the contents in
cell A to change. I wish for them to stay as a formula. Can this be done?
My setting under TOOLS is for the calc = Automatic.

I use massive formulas on a month-to-month basis and have to constantly
double-check my "copied from formulas" to ensure they are edited back to a
formula.
texbetty

I don't understand why your cell "A" should change....copying FROM "A" will
leave it as it was
unless you have some other routine that changes it without your knowledge

 

[Mike H]

Hi,

Copying a cell has no effect at all on the copied cell and paste
special(ing) it's values back to a worksheet likewise cannot change the
original cell from a fromula to a value. The only way the original cell can
be affected is if you paste the value back into a range the cell looks at it
could alter the result.

Mike

 

[FC]

If by any chance you mean that you don't want the formula to change ( same
column and same row from the original ) when you paste it in another cell
then do this: select the cell with the formula to copy.
go to the formula bar and highlight the whole formula.
press f4 once.
press enter.
copy and paste in another cell.
If this is not what you are looking for then please give more details.

 

[Evan]

Assuming FC identified your need, here are a couple possibilities.

If you have several formulas to copy, do a Find/Replace with =/zzz= to kill
the formulas. Then copy to another location and undo the kill with
zzz/(nothing). I do this when lookups are bogging down the calculations and
I replace formulas with values.

Consider using named ranges. Names don’t change when you copy or move
formulas. The formula must be devoid of battleship grid names. Setting up
named ranges is a bit of a nuisance, and using them is another effort. But
the formulas can be crystal clear: =Cats+Dogs instead of =A33+B19.

 

[texbetty]

Shucks - that didn't work, I must be doing something wrong. Please keep in
mind that when I'm doing this copy & paste special, I'm doing it with 5 rows
& 2 columns at a time usually. Would that make a difference?

original cell w/formula
=(437783.73)/1.18
=(261517.9)/1.18 =SUM(A8/$C$7)
=SUM(A7-A8) =SUM(A9/$C$7)
=(166081.89)/1.18 =SUM(A10/$C$7)
=SUM(A9-A10) =SUM(A11/$C$7)

when doing the copy/ps it changes org. to values
371003.161016949
221625.338983051 0.597367791626244
149377.822033898 0.402632208373756
140747.36440678 0.379369717554373
8630.45762711862 0.0232624908193824

"copy to" cells are values as they should be
371003.161016949
221625.338983051 0.597367791626244
149377.822033898 0.402632208373756
140747.36440678 0.379369717554373
8630.45762711862 0.0232624908193824

 

[Evan]

I thought you wanted to have the exact same formulas somewhere else. With

=(166081.89)/1.18 =SUM(A10/$C$7)
=SUM(A9-A10) =SUM(A11/$C$7)

you would do the the =/zzz= replacement to get

zzz=(166081.89)/1.18 zzz=SUM(A10/$C$7)
zzz=SUM(A9-A10) zzz=SUM(A11/$C$7)

which is text in the same place. Then copy/paste to another location (the
formulas are preserved) and do the zzz/(nothing) replacement there to get

=(166081.89)/1.18 =SUM(A10/$C$7)
=SUM(A9-A10) =SUM(A11/$C$7)

I forgot to point out that you need to do the zzz/nothing replacement were
you started to recover the formulas. Not slick, but it works on volume.

The Copy/PasteSpecial thing was intended to replace formulas with values,
but save
the formulas as text somewhere else to get them back if needed. So I might

Do the =/zzz= replacement.
Copy/Paste the text formulas to somewhere else
Do the zzz/(nothing) replacement in the original place
Do Copy/PasteSpecial-AsValues from and to the original place

 

[texbetty]

Many thanks to all.... that will work just fine.