Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel creates a file | Excel Discussion (Misc queries) | |||
How can I use a circular function to add 4 to a column of cells? | Excel Worksheet Functions | |||
Problem with IF function/circular/HELP! | Excel Worksheet Functions | |||
pasting creates 65000 lines | Excel Discussion (Misc queries) | |||
Combobox creates error | Excel Worksheet Functions |