SUM function creates circular ref if first or last rows are moved
 

hello
im working on a spreadsheet with rows of data that need to be dragged and
dropped to various categories. each category has it's own subtotal.

if i drag and drop either the first or last row that the subtotal
calculates, i get a circular reference error.

is there a way to maintain the integrity of my subtotals and still be able
to freely move the rows in each category?

i tried $col$row as a sticky but that did not work. please advise

SUM function creates circular ref if first or last rows are moved
 

hi, !

... a spreadsheet with rows of data that need to be dragged and dropped to various categories.
each category has it's own subtotal.
if i drag and drop either the first or last row that the subtotal calculates, i get a circular reference error.
is there a way to maintain the integrity of my subtotals and still be able to freely move the rows in each category?
i tried $col$row as a sticky but that did not work...

AFAIK... when you move/drag'n'drop cells/rows/columns...
and they are the beginning/bottom of a range reference in formulae/functions...
what you are 'really' doing is re/defining/dimensioning/... 'that' range reference

i would suggest you [whenever it's possible] to leave a 'tiny' row/column...
AS the beginning/bottom of a range reference in formulae/functions...
[you can set the heihgt/width so lower that they -almost- don't 'appear' on your data range]
so you can move/drag'n'drop whatever cells/rows/columns in the 'medium' part of your range reference

as an alternate [and possible] 'solution'... you should start to 'play' wit offset(... and row(<range.2.look... functions
to make a 'variable-range' reference for your formulae/functions -?-

if any doubts [or further information]... would you please comment ?
regards,
hector.