---- Examples where bug disappears ---- Example A (bug disappears) 1. Open LibreOffice (LO) Calc 2. In cell A2 enter: =SUM(B$1:B$1048575) 3. Right click on row heading 4 4. Select 'Insert Rows Above' 5. Cell A2 correctly shows: =SUM(B:B) 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B:B) Example B (bug disappears) 1. Open LibreOffice (LO) Calc 2. In cell A2 enter: =SUM(B$1:$B$1048575) 3. Right click on row heading 4 4. Select 'Insert Rows Above' 5. Cell A2 correctly shows: =SUM(B:$B) 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B:$B) Example C (bug disappears) 1. Open LibreOffice (LO) Calc 2. In cell A2 enter: =SUM($B$1:B$1048575) 3. Right click on row heading 4 4. Select 'Insert Rows Above' 5. Cell A2 correctly shows: =SUM($B:B) 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM($B:B) Example D (bug disappears) 1. Open LibreOffice (LO) Calc 2. In cell A2 enter: =SUM($B$1:$B$1048575) 3. Right click on row heading 4 4. Select 'Insert Rows Above' 5. Cell A2 correctly shows: =SUM($B:$B) 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM($B:$B) ---- Examples where bug appears ---- Example E (bug appears) 1. Open LibreOffice (LO) Calc 2. In cell A2 enter: =SUM(B1:B1048575) 3. Right click on row heading 4 4. Select 'Insert Rows Above' 5. Cell A2 incorrectly shows: =SUM(B1:B1048576) Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5} 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B1:B#REF!) Example F (bug appears) 1. Open LibreOffice (LO) Calc 2. In cell A2 enter: =SUM(B1:B$1048575) 3. Right click on row heading 4 4. Select 'Insert Rows Above' 5. Cell A2 incorrectly shows: =SUM(B1:B$1048576) Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5} 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B1:B$#REF!) Example G (bug appears) 1. Open LibreOffice (LO) Calc 2. In cell A2 enter: =SUM(B$1:B1048575) 3. Right click on row heading 4 4. Select 'Insert Rows Above' 5. Cell A2 incorrectly shows: =SUM(B$1:B1048576) Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5} 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: =SUM(B$1:B#REF!) Likewise converting to entire row is inconsistent (sometimes works sometimes produces #REF!) Thank you
What is the purpose of this bug report, considering that bug 92759 and bug 92751 have been closed as WontFix? Set status to NEEDINFO, please set it back to UNCONFIRMED once you have provided requested information. Best regards. JBF
Thank you Jean-Baptiste for your question. Perhaps I have caused some confusion if I made a few mistakes like: 1. changing bug 92759 and bug 92751 status to WONTFIX; 2. creating a new bug 92779 instead of adding to one of the previous ones. In bug 92751 Erike wrote “There's no automatic conversion to $B:B in between and should not be.” However, upon further testing there are cases where there *is* automatic conversion. Example cases are shown in bug 92779. Perhaps bug 92759 and bug 92751 status should be set to NEW as implementing this will assist in having feature parity and reduce the likelihood of creating #REF! in a formula. I think this bug is slightly different to bug 92759 and bug 92751 as it relates to changing the range in a formula for an entire column/entire row from A1:A1048576/A1:AMJ1 to A:A/1:1 notation in the event that single row/column is inserted. Thank you
(In reply to Óvári from comment #0) > Example E (bug appears) > 1. Open LibreOffice (LO) Calc > 2. In cell A2 enter: =SUM(B1:B1048575) > 3. Right click on row heading 4 > 4. Select 'Insert Rows Above' > 5. Cell A2 incorrectly shows: =SUM(B1:B1048576) > Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5} Note that this is different, example A has both absolute row references. References that result from inserting/deleting/shifting/moving and have not both anchors absolute are not displayed as entire col/row references. For user convenience, only when entering an expression and both anchors are relative it is taken as entire col/row reference, because that is what you also get when selecting a range with Shift+Ctrl+Down for example. > 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: > =SUM(B1:B#REF!) > Example F (bug appears) > 1. Open LibreOffice (LO) Calc > 2. In cell A2 enter: =SUM(B1:B$1048575) > 3. Right click on row heading 4 > 4. Select 'Insert Rows Above' > 5. Cell A2 incorrectly shows: =SUM(B1:B$1048576) > Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5} Mixed anchors, one relative one absolute. This will never yield an entire col/row reference. On purpose, because when copy&paste such reference the absolute part is sticky and the relative parts gets adjusted relatively to the new position. > 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: > =SUM(B1:B$#REF!) > > Example G (bug appears) > 1. Open LibreOffice (LO) Calc > 2. In cell A2 enter: =SUM(B$1:B1048575) > 3. Right click on row heading 4 > 4. Select 'Insert Rows Above' > 5. Cell A2 incorrectly shows: =SUM(B$1:B1048576) > Cell A2 should show: =SUM(B:B) {As shown in Example A, step 5} Again the same, mixed anchors. > 6. Repeat steps 3-5 and LO Calc correctly shows formula in A2 as: > =SUM(B$1:B#REF!) It is debatable what actually should happen with references that were not entire col/row but only become when inserting rows/cols. Excel is a bit more lax there and if both anchors are either absolute or relative, the reference is displayed as entire col/row reference. Not with mixed anchors. Also if inserting rows results in an entire column that the reference wasn't before, even if not displayed as A:A/1:1 because of mixed anchors, the reference anchors become sticky, regardless of relative or absolute addressing, and are not shifted when deleting rows or when inserting further rows. However, copy&paste =SUM(B$1:B1048576) one row further down still results in #REF! Maybe we could implement similar behaviour.
I'd love to see similar behaviour for selecting whole rows/columns as on Excel, just by clicking row number/column label. Currently really lack compared to Excel way of doing things.
Ari Latvala comment 4 refers to bug 92439.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cfecdd6199710921f8fd921f615203c9e34c551e sticky end col/row anchor for range references, tdf#92779 It will be available in 5.2.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.
Thank you Eike Rathke for this patch. Will the LibreOffice 5.0 Release Notes be updated? “Mixed absolute/relative anchors lead to a full range notation being displayed, e.g. A$1:A1048576 or A1:A$1048576 because Calc assumes the user did that on purpose.” https://wiki.documentfoundation.org/ReleaseNotes/5.0#A:A_.2F_1:1_entire_column.2Frow_references
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a0c4ce340d253d5f197f4e96676225f3f5f216de display as entire col/row also if both anchors relative, tdf#92779 It will be available in 5.2.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.
(In reply to Óvári from comment #7) > Will the LibreOffice 5.0 Release Notes be updated? No, because it will not be available in 5.0
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b35bfb9f439910c14dc6161534d66a5a51cb1121&h=libreoffice-5-1 sticky end col/row anchor for range references, tdf#92779 It will be available in 5.1.0.1. 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-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=afa8fec8367c747cbf50d81fe836dd1679b85867&h=libreoffice-5-1 display as entire col/row also if both anchors relative, tdf#92779 It will be available in 5.1.0.1. 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.
Created attachment 121183 [details] Row test with failures Thank you for also fixing bug 92759 and bug 92751 with this patch. 1. Please look at formulae in column C. They should be the same as the formulae in column A (without the ') 2. Select rows 30-34 (i.e. 5 rows) 3. Right click and select ‘Insert Rows Above’ 4. Look at formuilae in column C. They should be the same as the formulae in column B (without the '); however some are incorrect as shown in column D. Thank you
*** Bug 92759 has been marked as a duplicate of this bug. ***
*** Bug 92751 has been marked as a duplicate of this bug. ***
(In reply to Óvári from comment #12) > Created attachment 121183 [details] > Row test with failures That is the same scenario I already explained in comment 3, mixed absolute and relative addressing. If one row is addressed absolute and the other relative, the reference will never be displayed as an entire column reference.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d6f6459350e9e661bc09191cfb31c5e779d522de sticky end only if it already was a range on the same axis, tdf#92779 It will be available in 5.2.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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9c1ba0988f5db05bb796eaf7cf902a0b601c6736 geez, how about actually checking the Move() error return? tdf#92779 related It will be available in 5.2.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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fe445df126ff0be771494dfef3aec09ca82f8aef unit test for sticky end col/row anchors, tdf#92779 It will be available in 5.2.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-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=74397f2fab816c11b73c36ef70bed97ae146969e&h=libreoffice-5-1 sticky end only if it already was a range on the same axis, tdf#92779 It will be available in 5.1.0.1. 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-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=be39e7ea63423c1c9bab0d9410ffca93bdd474c4&h=libreoffice-5-1 geez, how about actually checking the Move() error return? tdf#92779 related It will be available in 5.1.0.1. 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-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=eaed8fe33bb677f668571771fa814b02ee1d6cca&h=libreoffice-5-1 unit test for sticky end col/row anchors, tdf#92779 It will be available in 5.1.0.1. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=21dfe37aca134b22cdbdb73fc29d30a89cd85524 separate ScRange::Move() and MoveSticky(), tdf#92779 It will be available in 5.2.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-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=478917a2c0fe7e7e3c79d9a12b4488128e6da969&h=libreoffice-5-1 separate ScRange::Move() and MoveSticky(), tdf#92779 It will be available in 5.1.0.1. 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.
Danke schön Eike Rathke! (Thank you)
*** Bug 94959 has been marked as a duplicate of this bug. ***