Cut and Paste is messing up my functions!!!!

[Mevidinous Nulle]

First I'd like to start by saying I'm a long time lurker and first
time poster.

Oh great gurus of Excel......

This should be easy.

I have a data area on my sheet. I also have an area full of functions
that use various cells in the data area for raw data.

I need to be able to CUT and paste the data within the data area. If
I CUT however, the formulas all change to reference where ever I paste
the cut data. I've tried making all the formulas use absolute
references but that doesn't help. I've tried protecting the whole
sheet (except for the data area of course) and that doesn't work
either. Yes, I am aware that I can COPY, paste, and delete instead of
CUT but gee, isn't there another way? Is there a way to globally turn
off this "helpful" behavior?

MN

 

[Peo Sjoblom]

One way

do edit replace, find what =
replace with a=

i.e. replace the equal sign in the formulas with a= thus making them into
text strings
do your data shuffle, then repeat the edit>replace but reverse it, replace
a=
with =



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Tom Ogilvy]

instead of a cell reference in your formula like A1 use Indirect("A1")

since "A1" is a text string and not a reference, it will not adjust.

 

[Mevidinous Nulle]

instead of a cell reference in your formula like A1 use Indirect("A1")

since "A1" is a text string and not a reference, it will not adjust.

Wow. You people are amazing. Thanks for the suggestions. The
"Indirect" function did the trick!

Thanks again

MN