1. Open LibreOffice Calc 2. Alt+T (Tools) 3. O (Options) 4. LibreOffice Calc → Formula 5. Formula Options → Formula syntax: Excel A1 6. OK 7. In cell B4, type: =SUM(D:D) 8. Right click on any row (except row 4), say row 15, and 'Delete Selected Rows' 9. The formula in cell B4 should be the same as in point 7 but has now incorrectly changed to: =SUM(D$1:D$1048575) 10. In cell B7, type: =SUM(10:10) 11. Right click on any column (except column B), say column F, and 'Delete Selected Columns' 12. The formula in cell B7 should be the same as in point 10 but has now incorrectly changed to: =SUM($A10:$AMI10) 13. The formula function could be replaced by another (eg. PRODUCT) and the same incorrect change occur. 14. I understand that LibreOffice will have the column and row selection syntax soon. When LibreOffice understands column and row selection the Formula Options → Formula syntax in points 5 could be left as: Calc A1. Hopefully this problem will be solved by then.
I can confirm with Version: 4.5.0.0.alpha0+ Build ID: b024e36ddb3b53163d7a01f6f7b5aadb7a858cd9 TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-03-31_09:12:20 and with LibreOffice 3.5.0 Build ID: d6cde02
I think a duplicate. Please if you are not agree reopen it. *** This bug has been marked as a duplicate of bug 44419 ***
Not a duplicate, but related.
Taking.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=dc34bb9b05cf7b5261122a73fa9c6f5356802c19 tdf#90573 do not shift/move entire col/row references and ranges It will be available in 5.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b07e0269e5dc266828c4d621d7daae085d644df4&h=libreoffice-5-0 tdf#90573 do not shift/move entire col/row references and ranges It will be available in 5.0.0.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Thank you Eike for your work enabling LibreOffice Calc to understand row and column notation. 1. Open LibreOffice Calc 2. Alt+T (Tools) 3. O (Options) 4. LibreOffice Calc → Formula 5. Formula Options → Formula syntax: Calc A1 6. OK It would be convenient if LibreOffice Calc also knew about the bounds being able to extend to the beginning/end of a column/row. 7. In cell B4, type one of the following: =SUM(D10:D) =SUM(D$10:D) =SUM($D$10:$D) =SUM(D$10:D) =SUM(D:D10) =PRODUCT(15:H15) =PRODUCT(H12:H) This will enable spreadsheets created with LibreOffice Calc to be robust if the number of rows/columns in varied in the future. This can also assist LibreOffice Calc's functions, i.e. VLOOKUP(), if there is information at the beginning of the column/row to only search in a subset of a column/row. If I understand correctly, Microsoft Office Excel has a feature where you can use the API to create spreadsheet with a number of cells greater than that which can be opened directly with Excel program.
That would be different feature request and not related to this specific bug. I'm setting this to RESOLVED again. However, I doubt we'd implement such special case references, given that not even Excel understands them. Bear in mind they'd have to be stored as D10:D1048576 in most file formats anyway.