Formulas should be automatically extended to newly inserted rows/columns at the beginning or end (for example if the cell where the action is done is within the formula cell range). STR/example with SUM: 1. A1: cell content "1" 2. A2: cell content "2" 3. A3: cell content "3" 4. A4: cell content "=SUM(A1:A3)" 5. Right-click on row header of row 3. 6. Select "Insert Rows Below". Current result: Cell content of A5 (formerly A4) still is "=SUM(A1:A3)". Expected result: Cell content of A5 should be "=SUM(A1:A4)". The automatic adjustment of the SUM formula would prevent the user to do it by himself/herself. I assume that the user want to have A4 added to SUM. It's questionable under which circumstances the formula should be adjusted automatically: * If the cell where the action is done is within the formula cell range. This would extend the formula to newly created A1 if the user clicks "Insert Row Above" in row 1 and this would behave also as described in the example above. * If the cell which is inserted is between SUM cell and its summed cells (in the example above: A1-A3). This would extend the formula to A4 if the user clicks "Insert Rows Above" in row 4. Would this also extend the formula to newly created A1 if the user clicks "Insert Row Above" in row 1? And is this technically distinguishable?
I cannot reproduce Version: 6.0.0.0.alpha1+ Build ID: 20f6242b2c50876754368eeadcdc8902cf76e79a CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group
Currently the formula is updated, if you move a cell referenced in it. In your steps you do not move A3, so nothing happens. The current behaviour would conflict with your proposed: "This would extend the formula to newly created A1 if the user clicks "Insert Row Above" in row 1" This sort of mind-reading is difficult and changed behaviour might cause users to be upset. A working solution for these simple "numbers + sum formula" things is to have one blank row above the sum row and to include it in the sum range. Now when you insert rows from the last row with numbers, the formula range is updated.
There is an option in: Tools - Options - LibreOffice Calc - General - Expand reference when new columns/rows are inserted. Check is option and then try the steps in your original bug report, it works perfect. That option is not checked by default, because when it is checked, the formula may change (expand) if new columns/rows are inserted, thus it's dangerous at some point - only enable this option when you know what you are doing. Close as WORKSFORME.
(In reply to Kevin Suo from comment #3) > There is an option in: > Tools - Options - LibreOffice Calc - General - Expand reference when new > columns/rows are inserted. > > Check is option and then try the steps in your original bug report, it works > perfect. > > That option is not checked by default, because when it is checked, the > formula may change (expand) if new columns/rows are inserted, thus it's > dangerous at some point - only enable this option when you know what you are > doing. > > Close as WORKSFORME. Thank you very much, Kevin. That's what I requested. I should read the help more carefully ...