Excel change SUM references if you cut & paste the first cell in SUM...

[Marie J-son]

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]

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]

Thank you Dave,

OK, maybe I should try offset. Right now however, I made a workaround by
restore the formulas with a worksheet_change procedure and with a
errorhandler "on error goto 0" take away the error box about cirular
references you get if you drag n drop / cut/paste the A9 cell to A4.

I have a number of tables where the users will like to move cells around, to
tables above and below. There will mostly be the "circular references error
box" poping up all the time.

Question: I shouldn't take away all error messages like I've done now, but
couldn't find a way to eliminate "error for circular references" only. Do
you or anybody else have a clue? I could't find a error number to check with
in a error handler. Maybe the offset method Dave decribed is better...?

/Regards

 

[Dave Peterson]

Ahhhh.

You always want your sum to point at the same range (A9:A13) no matter where the
users drag and drop cells??

If that's the case, you can use something like this:

=SUM(INDIRECT("A9:A13"))

Be aware that if you insert/delete rows or columns, then this formula will still
point at A9:A13. (that string "a9:a13" won't change like a cell reference.

 

[Marie J-son]

Thank you again!

Dave, I have tried both INDIRECT and OFFSET and they both work well. Do you
find INDIRECT better than OFFSET? It seems like OFFSET also keep the same
target sum area when I move around the cells, cut & paste etc. (I haven't
tried drag n' drop from other worksheets yet, hopefully that will not make a
differense).

It is not possible today to change rows or columns, it is locked, but maybe
I will develope it otherwice later on.

/Regards

 

[Dave Peterson]

If I understood your situation, I think I'd use the =indirect() version. I find
it easier to read/understand.

But inserting/deleting of rows/columns is a problem, then I'd use the =offset()
version.

I guess it depends <vbg>.

Thank you again!

Dave, I have tried both INDIRECT and OFFSET and they both work well. Do you
find INDIRECT better than OFFSET? It seems like OFFSET also keep the same
target sum area when I move around the cells, cut & paste etc. (I haven't
tried drag n' drop from other worksheets yet, hopefully that will not make a
differense).

It is not possible today to change rows or columns, it is locked, but maybe
I will develope it otherwice later on.

/Regards