Bug 144397 - FILEOPEN XLSX Named range is not resolved when it points to external file
Summary: FILEOPEN XLSX Named range is not resolved when it points to external file
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Balázs Varga
URL:
Whiteboard: target:7.3.0
Keywords: filter:xlsx
Depends on:
Blocks: XLSX
  Show dependency treegraph
 
Reported: 2021-09-09 06:17 UTC by NISZ LibreOffice Team
Modified: 2022-05-09 10:22 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
The External Link dialog of attachment 166828 in Calc - good (81.20 KB, image/png)
2021-09-09 06:17 UTC, NISZ LibreOffice Team
Details
The Manage Names dialog of attachment 166828 in Calc (48.08 KB, image/png)
2021-09-09 06:17 UTC, NISZ LibreOffice Team
Details
The document after recalculating (55.16 KB, image/png)
2021-09-09 06:17 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2021-09-09 06:17:09 UTC
Created attachment 174915 [details]
The External Link dialog of attachment 166828 [details] in Calc - good

This is split off from bug 137846
When a named range is defined in Excel such a way that it points to an external file, Calc does not resolve the external file reference in the Manage Names dialog and in the formulae that use this name.
The Edit – External Links dialog sees the connection to this target file correctly, but recalculating the sheet or changing data in the target file reveals that the named range does not work.

Steps to reproduce:
    1. Open attachment 166827 [details] and attachment 166828 [details] in Calc
    2. Press Ctrl-F3 to open the Manage Names dialog, see the NamedRangeInOtherFile name
    3. Close the dialog and press F9 to recalculate the formulae

Actual results:
The NamedRangeInOtherFile names range is [1]!MonthNames instead of EDATAE13.xlsx!MonthNames
Recalculating the sheet replaces the cached value in B2 cell with a #NAME? error.

Expected results:
The NamedRangeInOtherFile names range should be EDATAE13.xlsx!MonthNames
Recalculating the sheet should keep the value in cell B2.

LibreOffice details:
Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: c7b5e6566d9b24a0a996c739a945004d9aadee2f
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL

Also happens in 7.0, 6.0, 5.3.
In 5.2 and before not even the connection to the external file was imported correctly in the External Links dialog.
Comment 1 NISZ LibreOffice Team 2021-09-09 06:17:31 UTC
Created attachment 174916 [details]
The Manage Names dialog of attachment 166828 [details] in Calc
Comment 2 NISZ LibreOffice Team 2021-09-09 06:17:46 UTC
Created attachment 174917 [details]
The document after recalculating
Comment 3 m.a.riosv 2021-09-21 15:00:42 UTC
*** Bug 137847 has been marked as a duplicate of this bug. ***
Comment 4 Commit Notification 2021-10-11 07:03:55 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/12ee423c7549ddd2b86dfc3fc6fed2c617dcca7f

tdf#144397 tdf#144636 XLSX: cache external named ranges and their formulas

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 5 NISZ LibreOffice Team 2021-11-09 07:51:08 UTC
Verified in: 

Version: 7.3.0.0.alpha1+ (x64) / LibreOffice Community
Build ID: 73334560b2dd2d60ac58d2cc2b1a5295490b03e1
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded