I think the developers had to make a decision. The one that they came up with
was if you cut a cell out of the middle of a range, then trust the user to know
that they don't want to change that formula.

But if they cut a cell from a cell in that formula (top or bottom cell), then
they figured that the user wanted to extend the range to include the new
position of the pasted cell.

(It seems like a pretty reasonable approach to me--I'm not sure I'd want any
other way.)

Possible work arounds...
Use copy instead of Cut. then Clear the cell in the original location after you
pasted.

modify the formula to be based on the position of the cell with the formula.

In A8:
Instead of
=sum(a9:a13)
how about:
=SUM(OFFSET(A8,1,0,4,1))

Starting in A8, come down 1 row, to the right 0 columns for a total of 4 rows
and 1 column)

You may want to look at David McRitchie's site to see how he explains using
=offset() to keep formulas from adjusting.

http://www.mvps.org/dmcritchie/excel/offset.htm

You might find something a little more useful.

Marie J-son wrote:

Hi,

A problem came from a user today. Set up: IN A1: " =SUM(A2:A6) " and in A8:
" =SUM(A9:A13) " and fill in some figures in A9:A13.

Cut A10 and paste it to A4. everything is ok.
If I cut A9 you get "Circular Reference error, because the formula in A8 has
changed to: " =SUM(A4:A13) " and the old SUM it still there in A8.

Why? Should it be that way, and what is the purpose? Any workarounds?

/Kind regards

--

Dave Peterson