Bug 100818 - add ability to refer to local names in another worksheet to INDIRECT
Summary: add ability to refer to local names in another worksheet to INDIRECT
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:7.1.0
Keywords:
: 123790 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2016-07-09 00:25 UTC by sebalis
Modified: 2021-09-23 19:14 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
test case as described in the bug description (8.16 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-07-09 00:25 UTC, sebalis
Details
test case with named areas (9.67 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-09-23 14:49 UTC, jan.stuehler
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sebalis 2016-07-09 00:25:11 UTC
Created attachment 126131 [details]
test case as described in the bug description

Bug #96915 added the capability to refer to names that are defined in another worksheet and have local scope there with the normal syntax for names, i.e. Sheet!Name. Before that, this notation only worked for names either having local scope in the same worksheet or global names. But what is still missing is for INDIRECT to deal with this situation too. The attached spreadsheet "local-name-test.ods" has a local name "pi_value" in Sheet2, and Sheet1 contains one cell refering to with in the formula =Sheet2!pi_value and another cell with the formula =INDIRECT("Sheet2!pi_value"). In LibreOffice 5.2.0.1 the first cell works, the second gives a #REF! error. I don’t know Calc well enough to be certain that there is no other function that would have to be adapted to deal with references of this kind, so I’d be grateful if whoever gets to work on this bug could give this some thought. Thank you for fixing Bug #96915 and for considering this one.
Comment 1 m_a_riosv 2016-07-09 14:07:59 UTC
Reproducible
Win10x64
Version: 5.3.0.0.alpha0+
Build ID: 757f221bceb74ccc2af8a9d4de149076280f29fb
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-06-23_23:54:55
Comment 2 QA Administrators 2018-07-24 02:37:07 UTC Comment hidden (obsolete)
Comment 3 sebalis 2018-07-24 14:52:27 UTC
The bug is still present in 6.06rc1.

Version: 6.0.6.1
Build ID: 1:6.0.6~rc1-1~bpo9+1
CPU threads: 4; OS: Linux 4.9; UI render: default; VCL: gtk3; 
Locale: en-GB (en_DE.utf8); Calc: group

I don’t see much point in testing for a regression, because before Eike Rathke fixed bug #96915 (also reported by me) regarding the same issue outside INDIRECT, it would have been unthinkable that this would have worked. And ever since that other was fixed, I have been intermittently checking whether it would work – it never did.
Comment 4 sebalis 2019-02-20 04:48:56 UTC
The bug is still present in Libreoffice 6.2.0.3 (Arch Linux version number). I am finding it hard to understand that a formula that works outside of INDIRECT works but when given to INDIRECT as a string it does not.
Comment 5 Eike Rathke 2019-02-20 12:43:36 UTC
(In reply to sebalis from comment #4)
> I am finding it hard to understand that a formula that works outside of
> INDIRECT works but when given to INDIRECT as a string it does not.
You sure dug into the code and at least tried to understand what's happening there?
Comment 6 Eike Rathke 2019-02-20 12:44:15 UTC
Could be suitable for an interesting EasyHack, marking as such.
Code pointer:
sc/source/core/tool/interpr1.cxx ScInterpreter::ScIndirect() and follow / step through in debugger what's happening there for a sheet local scope name.
Comment 7 sebalis 2019-02-20 13:56:05 UTC
> You sure dug into the code and at least tried to understand
> what's happening there?

No, sorry. I commented from the point of viewer of a user who has worked as a software developer, but not with C++. I did not expect to be able to gain insights in an appropriate amount of time so I’ve never looked at the LibreOffice code. What I did think is that the task is the same – parsing a string into a structure representing the formula, and then executing it – so I really thought that your work on bug bug #96915 (thanks again) would have covered the INDIRECT case immediately.
Comment 8 m_a_riosv 2019-03-01 22:03:31 UTC
*** Bug 123790 has been marked as a duplicate of this bug. ***
Comment 9 Commit Notification 2020-10-31 10:47:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/075da6f2463c922bcb8c553949756af4e8e103e0

Resolves: tdf#100818 Support sheet-local scoped names in INDIRECT()

It will be available in 7.1.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 10 sebalis 2020-10-31 10:54:58 UTC
Thank you very much, Eike! There’s a real-world application with an ugly workaround which I will have to update later this year anyway; I look forward to converting it back to using this feature. :-)
Comment 11 Commit Notification 2020-11-02 18:25:07 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8c4847fb88059259f17d002c4fd84ef82937c2f3

tdf#100818: sc_ucalc: Add unittest

It will be available in 7.1.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 12 Xisco Faulí 2020-11-02 19:17:01 UTC
Verified in

Version: 7.1.0.0.alpha1+
Build ID: 35f7d9a18fa7f559a1427e1b8a0f094f864f945a
CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

@Eike, thanks for fixing this issue!!
Comment 13 sebalis 2020-11-12 05:27:34 UTC
I tested it with a current nightly and it worked. Thanks! I was surprised that the INDIRECT variante doesn’t seem to take the same syntax as a direct formula. With INDIRECT I need to use "Sheet!Name" as I had it in my test file (attached to this bug). The syntax displayed in the direct case seems to have changed since I created my file, it is not Sheet.Name (in my test file, Sheet2.pi_value). The INDIRECT variant will not work with the dot as a separator, only with the exclamation mark it seems. I don’t really mind, but would have thought that one would want to use the same forms.
Comment 14 Eike Rathke 2020-11-12 18:51:26 UTC
The sheet name separator INDIRECT() supports depends on the "Reference syntax for string reference" setting under Tools > Options > Calc > Formula, Detailed Calculation Settings, Custom. In your document it is explicitly set to "Excel R1C1" so only the '!' separator is supported. Same would be the case for "Excel A1". "Calc A1" would support only '.' separator, "Calc A1 | Excel A1" would try both, in order. The setting is saved with the document, in .xlsx format to a custom property so it's even preserved there. If a .xlsx document is loaded and the property is not present, i.e. as saved by MS-Excel, then "Excel A1" is assumed, IIRC..
Comment 15 Eike Rathke 2020-11-12 18:54:17 UTC
I forgot, if set to "Use formula syntax" it follows the Formula syntax set under Tools > Options > Calc > Formula, Formula Options.
Comment 16 jan.stuehler 2021-09-23 14:47:53 UTC
This seems to work for a name for a single cell and not for a name for multiple cells.

I took the attached "test case as described in the bug description" local-name-test-1.ods and created a 3x3 matrix J7:M10 in Sheet2.
I marked the cells K7:M7 and named them sbereich ("column_range").
I marked the cells J8:J10 and named them zbereich ("row_range").

In Sheet1
 - =MATCH(J8;Sheet2.sbereich;0) returns 1 correctly
 - =ADDRESS(MATCH(I10;Sheet2.zbereich;0);MATCH(J8;Sheet2.sbereich;0)) returns $A$2 correctly
 - =INDIRECT(ADDRESS(MATCH(I11;Sheet2.zbereich;0);MATCH(J8;Sheet2.sbereich;0))) returns #REF

 - =MATCH(K8;"Sheet2!sbereich";0) returns Err:504
 - =ADDRESS(MATCH(I10;"Sheet2!zbereich";0);MATCH(K8;"Sheet2!sbereich";0)) returns Err:504
 - =INDIRECT(ADDRESS(MATCH(I11;"Sheet2!zbereich";0);MATCH(K8;"Sheet2!sbereich";0))) returns Err:504

Please see attached local-name-test-2.ods.
Comment 17 jan.stuehler 2021-09-23 14:49:00 UTC
Created attachment 175223 [details]
test case with named areas
Comment 18 Eike Rathke 2021-09-23 19:14:40 UTC
The document has its "Reference syntax for string reference" explicitly set to Excel R1C1 (under Tools - Options - Calc - Formula, Detailed Calculation Settings, Custom, Details. But the ADDRESS() function in J10 and J11 produces A1 style syntax ($A$2 and $A$3). INTERPRET() uses the string reference setting, that can't work and hence the #REF! error.

Either set the string reference setting to any of the A1 syntax (depending on what else your document uses), or explicitly force ADDRESS() to produce R1C1 syntax, and best then also tell INDIRECT() to use that, so:

=INDIRECT(ADDRESS(MATCH(I11;Sheet2.zbereich;0);MATCH(J8;Sheet2.sbereich;0);1;0);0)

For the Err:504 in K9:K11, all these pass a string as second argument to the MATCH() function. That can't work. It must be a cell range reference instead.