Keeping forumla cell reference after cut and pasting

[Eli]

I am trying to cut and paste data, while maintaining the same forumula
references that I had before. For example before I cut and paste lets say the
formula is:

=$A$1+$B$1

Now lets say I want to move the data in A1 and B1 over one spot, so I cut
the two cells and paste them in B1 and C1. Excel automatically updates the
formula to:

=$B$1+$C$1

How do I make the formula stay as it was orginally?

Thanks for your help.

 

[David Biddulph]

=INDIRECT("$A$1")+INDIRECT("$B$1")

 

[Peo Sjoblom]

One way

replace the formula with


=INDIRECT("A1")+INDIRECT("B1")


or for that particular formula

=SUM(INDIRECT("A1:B1"))

no need to use the dollar signs since it is a text reference inside INDIRECT

--


Regards,


Peo Sjoblom

 

[JMay]

perhaps a bit crude, but if you don't have to do this routine much before
copying you can edit the formula to "z=$A$1+$B$1" (without the quotes)
then Copy and paste. In the pasted cell - Edit (F2) and remove the "z".
Done

 

[Kay]

Eli,

Thought I would pass along a very simple technique. IF you copy the formula
within the formula edit bar and use the check mark before you leave the
formula bar all "Pastes" will remain as your wrote it.