The following used to be the correct behaviour since the beginning of OpenOffice.org: WITHOUT option "Expand References" -- any insertion at the first row of the referenced range shifts down the reference. -- any insertion directly below the referenced just inserts cells _below_ the reference without affecting the reference. WITH option "Expand References" -- any insertion at the first row of the referenced range expands the reference. -- any insertion directly below the referenced expands the reference. In both cases any insertion between row #2 and the last row expands the reference. ########################################################################### So far LibreOffice behaves consistently, nevertheless some flaw has been introduced in version 6, may be earlier. OpenOffice used to preserve the formulas adjacent to the inserted cells whereas LibreOffice consequently expands all references which flaws some calculated fields such as running totals. In the attached document, the last reference of the running total expands beyond the current row if "Expand References" is set. Formula in B11 =SUM($A$6:A11) becomes =SUM($A6:A12) although the cell B11 does not move. The not adjacent formula cell in B2 which calculates the overall =SUM($A$6:$A$11) expands to =SUM($A$6:$A$12) which is perfectly wanted behaviour. However, the adjacent calculated field should not change. This becomes more apparent when you turn on the R1C1 formula view. The entire calculated field is calculated as =SUM(R6C1:RC[-1]) describing the sum from absolute Row6,Col1 until THIS row, previous column. After inserting a new row, the last formula changes to =SUM(R6C1:R[1]C[-1]) describing the sum from absolute Row6,Col1 until NEXT row,previous column.
Created attachment 155885 [details] Spreadsheet with total and a calculated field of running totals.
Created attachment 155888 [details] excel spreadsheet (In reply to Andreas Säger from comment #0) > The not adjacent formula cell in B2 which calculates the overall > =SUM($A$6:$A$11) expands to =SUM($A$6:$A$12) which is perfectly wanted > behaviour. correct: =SUM($B$6:$B$11) expands to =SUM($B$6:$B$12) > Formula in B11 =SUM($A$6:A11) becomes =SUM($A6:A12) although the cell B11 > does not move. However, the adjacent calculated field should not change. with AOO 4.1.5 and "[x] expand references": - select row 12 - context menu: insert row -> formula in B11 changes from =SUM($A$6:A11) to =SUM($A$6:A12) i cannot see a difference? btw: excel suggest using: =SUM(INDIRECT("A6:A"&ROW()))
> with AOO 4.1.5 and "[x] expand references": > - select row 12 > - context menu: insert row > -> formula in B11 changes from =SUM($A$6:A11) to =SUM($A$6:A12) > The formula does not calculate the running total anymore. The meaning of this single formula in B11 has changed. It does not sum up the other column until the same row. The R1C1 notation shows this more clearly. > btw: excel suggest using: =SUM(INDIRECT("A6:A"&ROW())) https://support.office.com/en-us/article/Calculate-a-running-total-in-Excel-1359BF89-180B-4771-B5B4-C6F6558549C5 describes the proceeding with normal formulas, cell insertion and formula copy. I have to assume that Excel does not modify the existing formula because this would mix wrong formulas into the calculated field. And OpenOffice still shows the correct behaviour where certain references do NOT expand. At the moment I have difficulties describing a rule for this. The old source code should explain it better than I can right now. It could be that references adjacent to the inserted cells and in the opposite direction to the move direction remain untouched but I'm not sure.
Created attachment 155898 [details] screenshots from aoo415 with expand referenced enabled
Please mark this as invalid or enhancement request? I don't know. Totally confused sincerely yours, Andreas
(In reply to Andreas Säger from comment #5) > Please mark this as invalid or enhancement request? I don't know. > Totally confused sincerely yours, > Andreas ok, setting to WFM I don't think, it's possible to distinguish between formulas which should expand or not expand if "expand references" is enabled.