Thank you Eike Rathke for enabling cell highlighting and formula colors for entire column(s)/row(s) notation. It made it easier to find the bug below. 1. In cell C7 enter: 1 2. In cell A1 enter: =SUM(B:$C) 3. Press Enter 4. Click on cell A1 5. Ctrl+C (copy) 6. Highlight cells B1:E1 7. Ctrl+V (paste) 8. LibreOffice Calc outputs are: Cell Formula Value Expected value A1 =SUM(B:$C) 2 Err:522 {Circular reference} B1 =SUM(C:$C) 1 Err:522 {Circular reference} C1 =SUM(D:$C) 0 Err:522 {Circular reference} D1 =SUM(E:$C) 0 Err:522 {Circular reference} E1 =SUM(F:$C) 0 Err:522 {Circular reference} LibreOffice Version: 5.1.0.0.alpha1+ (x64) Build ID: ab23efcc2bb9ca178dee16b3d01eabb3f80d8e17 TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-06-30_19:11:33 Locale: en-AU (en_AU) 9. Microsoft Excel advises there is a circular reference with: a) a dialog box; and b) the status bar shows: Circular References: C1 10. Microsoft Excel outputs are: Cell Formula Expected formula Value Expected value A1 =SUM(B:$C) =SUM(B:$C) 1 Err {Circular reference} B1 =SUM(C:$C) =SUM(C:$C) 1 Err {Circular reference} C1 =SUM($C:D) =SUM(D:$C) 1 Err {Circular reference} D1 =SUM($C:E) =SUM(E:$C) 1 Err {Circular reference} E1 =SUM($C:F) =SUM(F:$C) 1 Err {Circular reference}
I made a mistake in what I wrote at 2015-07-01 03:04:02 UTC in the section regarding Microsoft Excel. The 'Expected formula' are incorrect and the 'Formula' (which is actually output by Excel) is correct. LibreOffice Calc should also store ranges in the format LowerBound:UpperBound. This may: 1. simplify error finding such as circular references; and 2. make LibreOffice Calc consistent with Microsoft Excel. Whether a formulae is entered or pasted, LibreOffice Calc should check and store each range in the LowerBound:UpperBound order. Thank you
Eike Rathke work in enabling correct references in LibreOffice is to be congratulated. As Jerzy Tyszkiewicz has raised a concern about LibreOffice Calc formulae capabilities, it might be advisable to continue to use Excel until the resolution of this circular reference. LibreOffice Calc formula does not correctly flag a circular reference and provides in the spreadsheet while Excel does flag the circular reference correctly. Thinking further a solution to ensure that any formulae entered or pasted, LibreOffice Calc should check and change if necessary each range in the LowerBound:UpperBound to be in ascending order for both column and row. For example: B10:B1 should be changed and stored as B1:B10 C:A --> A:C A3:B2 --> A2:B3 B2:A3 --> A2:B3 $E3:C$8 --> C3:$E$8 The Name Box in LibreOffice Calc automatically list the range correct if you select cells from E8 to C3, or E3 to C8, or C3 to E8 or C8 to E3. Perhaps the code in the Name Box could be reused to change the formulae in ascending order. Thank you
@Ovari: Again, would you please stop advising the use of a different product? Thank you.
Actually this isn't even related to A:A/1:1 notation, it happens the same when copying =SUM(B$1:$C$1048576)
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d24c6a0280b0287ee6c23ca89068323c6b7c3dd7 always justify a referenced range in order, tdf#92468 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ad3d2b6c2e88d191d76f90eb5be927f7ca76c670 (re-)introduce ScComplexRefData::PutInOrder(), tdf#92468 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=369ee0b1faf79f1bd23c75ee04dd0dcc5bf283af introduce ScTokenArray::AdjustReferenceOnCopy(), tdf#92468 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3ddaeaab37d585971e376de6ad7b0f06f55f2e1a call ScTokenArray::AdjustReferenceOnCopy() in ScFormulaCell clone, tdf#92468 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.
Pending review https://gerrit.libreoffice.org/16815 for 5-0
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2873d7586199adaaf43144d6b6d4b2f253cfe4dd unit test for copy&paste put reference in order, tdf#92468 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.
Pending review https://gerrit.libreoffice.org/16829 for 4-4
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9bb4b60fd6deb348af37d9a51860e4d554ffbc35&h=libreoffice-4-4 always justify a referenced range in order, tdf#92468 It will be available in 4.4.6. 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=21d5e7bbb5ba986a3fd2b4194f83c18003fc3bac&h=libreoffice-5-0 always justify a referenced range in order, tdf#92468 It will be available in 5.0.0.3. 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-4-4-5": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d004a44336f58b7acc146c7d27707f142797042f&h=libreoffice-4-4-5 always justify a referenced range in order, tdf#92468 It will be available in 4.4.5. 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.
*** Bug 87709 has been marked as a duplicate of this bug. ***