(Issue also exists in LibO 4.3.3.2) Problem description: Once you have created one or several matrices of cells in a spreadsheet, you cannot add or delete rows anywhere between the first and the last row of any of the matrices. You cannot even insert several rows at once right above a cell matrix (as soon as the selection before the insertion makes you select some rows of the matrix). To perform the row insertion/deletion, you have to select every matrix of cells, delete their contents (especially the matrix formulas), do the insertion/deletion, and recreate the matrices from scratch (assuming you copied the matrix formulas somewhere else before deleting them). When the spreadsheet contains a matrix of several hundreds cells (or worse, several such matrices), this is quite painful and user-unfriendly. How to reproduce the issue, starting with an empty spreadsheet: - Check step: select rows 2 to 4 and right-click on the row number column: "Insert Rows Above" and "Deleted Selected Rows" are part of the pop-up menu items. This is OK. - Check step: select rows 3 to 6 and right-click on the row number column: "Insert Rows Above" and "Deleted Selected Rows" are part of the pop-up menu items. This is OK. - Now, select cells B4 to B8. - Type "=1+1" then Ctrl+Shift+Enter. Note that from now on, when current cell is B4, B5, B6, B7 or B8, formula in formula bar is shown within curly brackets ("{=1+1}"). - Select rows 3 to 6 and right-click on the row number column: no more "Insert Rows Above" and "Deleted Selected Rows" in the pop-up menu. This is not OK. - Select rows 2 to 4 and right-click on the row number column: no more "Deleted Selected Rows" in the pop-up menu. No more "Insert Rows Above" either, despite the fact inserted rows would be outside the cell matrix anyway. This is not OK. Note that changing the setting "Tools > Options > LibreOffice Calc > General > Extend references when columns/rows are added" does not change anything here. Expected result: Row insertion/deletion should be possible within or right above a matrix of cells. Note that Microsoft Excel allows inserting/deleting rows in cell matrices, and it properly extends formulas within them.
Confirmed, waiting for more savvy folks to comment. Changed severity to normal, check this flowchart: https://wiki.documentfoundation.org/images/0/06/Prioritizing_Bugs_Flowchart.jpg Win 7 64-bit Version: 4.4.0.0.alpha2+ Build ID: b021b5983c62e266b82d9f0c5c6d8d8900553827 TinderBox: Win-x86@39, Branch:master, Time: 2014-11-12_01:10:08
(In reply to Olivier DESCOUT from comment #0) > Note that Microsoft Excel allows inserting/deleting rows in cell matrices, > and it properly extends formulas within them. Excel 2010 doesnt allow insert/delete row into matrix. error message: You cannot change part of an array. https://support.office.com/en-za/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7
Although the ability to extend an array or matrix is a design decision, this bug breaks the ability to insert multiple rows at a time right above a matrix. I use it like this: when I would like to add say 5 new rows to a spreadsheet, I select 5 rows right under where I want them, then I click "Insert rows above". When the selection overlaps with an array, I cannot use this anymore, so I have to create rows one by one. Furthermore, it seems impossible to insert a row above an array/matrix if the array sits on the first row of the spreadsheet. Those may be two separate issues: - Inserting rows or columns inside a matrix - Inserting rows right above a matrix / inserting columns to the left of a matrix
They way to insert rows/columns inside a matrix, it's insert the rows/columns in the range referenced by the matrix. To insert a row above the first row of the matrix go to a cell in the same row out of the matrix and insert the row. A last option it's cut the matrix, insert the rows an past the matrix on their position. I think we can close as not a bug, if you are not agree please reopen it.