Bug 100472 - FILEOPEN: Formula referring cell in another sheet in an XLS is incorrect
Summary: FILEOPEN: Formula referring cell in another sheet in an XLS is incorrect
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: interoperability
Keywords: bibisected, bisected, filter:xls, regression
Depends on:
Blocks:
 
Reported: 2016-06-18 19:38 UTC by Aron Budea
Modified: 2016-08-05 00:53 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Aron Budea 2016-06-18 19:38:30 UTC 
Bug 77967 reported a crash in Windows during a specific imported XLS document: https://bugs.freedesktop.org/attachment.cgi?id=96393 (coming from bug 76611).

The crash has been gone by LO 5.0.0.5, but the formula is incorrect.
wrong G10: =$TPM.EU340
correct G10: =$TPM.FA349

There was no crash in Linux, there formula in G10 is correct in 4.2.0.4, incorrect in 4.4.0.3.
Note that because of bug 76611 G10 shows incorrect result in 4.2.0.4 (5.00 vs. the correct 5232.00 in 4.0.0.3), but the formula itself is correct.
Comment 1 Buovjaga 2016-06-18 20:11:30 UTC 
Confirming incorrect formula.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.3.0.0.alpha0+
Build ID: c13f60e7cd18df6b0ab70289f5b91ee01e4ae126
CPU Threads: 8; OS Version: Linux 4.6; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8)
Built on June 18th 2016
Comment 2 Aron Budea 2016-07-15 02:59:46 UTC 
# bad: [dda106fd616b7c0b8dc2370f6f1184501b01a49e] source-hash-0db96caf0fcce09b87621c11b584a6d81cc7df86
# good: [5b9dd620df316345477f0b6e6c9ed8ada7b6c091] source-hash-2851ce5afd0f37764cbbc2c2a9a63c7adc844311
git bisect start 'latest' 'oldest'
# bad: [0c30a2c797b249d0cd804cb71554946e2276b557] source-hash-45aaec8206182c16025cbcb20651ddbdf558b95d
git bisect bad 0c30a2c797b249d0cd804cb71554946e2276b557
# bad: [770ff0d1a74d2450c2decb349b62c5087e12c46b] source-hash-549b7fad48bb9ddcba7dfa92daea6ce917853a03
git bisect bad 770ff0d1a74d2450c2decb349b62c5087e12c46b
# good: [227af65db5e34efcf8dcb0b53333efecd30f37f8] source-hash-193c7ba9be48f00b46f9e789f233db577e7b3303
git bisect good 227af65db5e34efcf8dcb0b53333efecd30f37f8
# good: [78b395d05689a5207f2ec4cc29ec296d64076a96] source-hash-a2e4be6ded508030a6c2a33919cbe8cb504382e0
git bisect good 78b395d05689a5207f2ec4cc29ec296d64076a96
# good: [8dd6442885c969ae43ae5ff9ddfc53c9f04a9c27] source-hash-d07f0997c54e9cef31d996ebeb2aabfb4b4e0265
git bisect good 8dd6442885c969ae43ae5ff9ddfc53c9f04a9c27
# bad: [56e2ff1d44b7bcd4fff6ce86c93fd9b666808d0b] source-hash-d7794d2584cd5d476b011b5344c77ad59c179c58
git bisect bad 56e2ff1d44b7bcd4fff6ce86c93fd9b666808d0b
# bad: [00b5c570d73539704807312f0ad6a891c0dc0a71] source-hash-452623cf22c972f4f3865c2e7db961a73021d169
git bisect bad 00b5c570d73539704807312f0ad6a891c0dc0a71
# good: [1c8efc6d0240d080a0475dd7c15cfd7dc98c3b45] source-hash-ebe0cc11dadd57fcee5a8dc18278809e58db0075
git bisect good 1c8efc6d0240d080a0475dd7c15cfd7dc98c3b45
# good: [4af2f8831eaacc75fad15765d6ce2ad6c6dd9d95] source-hash-04fa50e39888926e1efdb52d46e6ab9bcd9cbea8
git bisect good 4af2f8831eaacc75fad15765d6ce2ad6c6dd9d95
# good: [bbe2b56dcde79293728338fa1850312c9654d3bb] source-hash-4c9cf98819037fdb70cbe68f678f6498d5646736
git bisect good bbe2b56dcde79293728338fa1850312c9654d3bb
# good: [967c71f85520df576a647ec1e94877de48dd1b0d] source-hash-c5e32fd8f25a83c63717c6a284ecf860054e9389
git bisect good 967c71f85520df576a647ec1e94877de48dd1b0d
# good: [bd563dbf0d292b97de2dc61c4f1785b70dc56324] source-hash-87e0ae677a7d39e4890d470a3537113c9ab2fa95
git bisect good bd563dbf0d292b97de2dc61c4f1785b70dc56324
# bad: [517e95915245b43e9a89b334854a6a62435b5cbe] source-hash-b010e6724fa5cd519daafd3abf57f0a3257025bb
git bisect bad 517e95915245b43e9a89b334854a6a62435b5cbe
# first bad commit: [517e95915245b43e9a89b334854a6a62435b5cbe] source-hash-b010e6724fa5cd519daafd3abf57f0a3257025bb
 517e95915245b43e9a89b334854a6a62435b5cbe is the first bad commit
commit 517e95915245b43e9a89b334854a6a62435b5cbe
Author: Matthew Francis <mjay.francis@gmail.com>
Date:   Wed May 27 17:32:58 2015 +0800

    source-hash-b010e6724fa5cd519daafd3abf57f0a3257025bb
    
    commit b010e6724fa5cd519daafd3abf57f0a3257025bb
    Author:     Markus Mohrhard <markus.mohrhard@collabora.co.uk>
    AuthorDate: Tue Dec 30 01:16:53 2014 +0100
    Commit:     Markus Mohrhard <markus.mohrhard@googlemail.com>
    CommitDate: Tue Dec 30 01:21:54 2014 +0100
    
        relative refs wrap around in xls, fdo#84556
    
        Change-Id: If84d468d4bd55ed55ccd517d0b078d283a1a9c38
Comment 3 Markus Mohrhard 2016-08-05 00:12:21 UTC 
According to Excel 2016 TPM.EU340 is the correct formula for G10.
Comment 4 Markus Mohrhard 2016-08-05 00:46:43 UTC 
Actually G11 is wrong but no idea why. In MSO 2016 it points to TPM.EU340 while it points to TPM.EU341 in calc.

This looks like a problem around shared refs with different anchor positions.
Comment 5 Markus Mohrhard 2016-08-05 00:53:27 UTC 
And I'm unable to reproduce it in a new document.

So let us just assume that it is somewhat broken in the file already. The file is too huge and even make mso-dumper choke.