Description: expand references when new columns/rows are inserted - I have seen many sheets not adding up all cells above sum update references when sorting range of cells - i have seen many sheets pointing to a wrong cell after sorting. just an idea, may be ask the user real time what should be done ! I have to set these values every time I install a new version (deb packages) Actual Results: possible wrong computations Expected Results: correct cell refferences Reproducible: Always User Profile Reset: No Additional Info:
(In reply to Ferdinand from comment #0) > expand references when new columns/rows are inserted > - I have seen many sheets not adding up all cells above sum > update references when sorting range of cells > - i have seen many sheets pointing to a wrong cell after sorting. Can you please give an example what exactly you have in mind?
Probably something like values in A1:A3 and in A4 have =SUM(A1:A3), then insert a row above 4 making it new row 4 and shifting A4 down to A5; what may be expected here is that the formula reference gets adjusted to =SUM(A1:A4) to include the new cell of the inserted row. Which happens if "expand references" is on (Tools -> Options -> Calc -> General). However, having that option on by default is not a good idea because it acts on any edge, top, left, right and bottom, and all range references anywhere referencing those, which may come unexpected. What might be useful could be to detect the special case of formula cells immediately at the insertion position referencing the cell range directly above and adjust those. But then again other formula expressions elsewhere might be out of sync, so that isn't a good idea either. Maybe a restricting second "expand only bottom references" option (radio button none|bottom|alledges) for the common case where one wants to append new data. Btw, sorting is completely off-topic in this context.
(In reply to Eike Rathke from comment #2) > ... "expand references" is (Tools -> Options -> Calc -> General). Is this working for you, Ferdinand?
Yes it is working once set. I agree with Eike, automatic expansion should be default for cells which are just above the group function (sum,avg, min,max ...) OR if the group function is on top, if a row is inserted above, below the selected block. best solution probably is to open a popup and ask ....
(In reply to Ferdinand from comment #4) > best solution probably is to open a popup and ask .... Could be annoying in many situations. Perhaps with a "Don't show again" option. But let's resolve the request as WFM as long no more requests are done.