Bug 96915 - add ability to refer to local names in another worksheet
Summary: add ability to refer to local names in another worksheet
Status: RESOLVED 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
QA Contact:
URL:
Whiteboard: target:5.2.0
Keywords:
: 92912 (view as bug list)
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
 
Reported: 2016-01-06 02:31 UTC by sebalis
Modified: 2017-07-11 22:41 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
File with a local name "name1" defined in Sheet1 (7.97 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-01-07 02:59 UTC, sebalis
Details
In sheet copy global name is in fact local name (9.15 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-06-26 07:33 UTC, royerjy
Details
test case as described in comment #17 (8.16 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-07-07 23:21 UTC, sebalis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sebalis 2016-01-06 02:31:19 UTC
If a name is defined with worksheet scope, there still are valid use cases where another worksheet needs to refer to that name. My example is a spreadsheet document that contains monthly recurring calculations, each in similarly structured worksheets that define and use equally named cells. To make this work, the names need to be defined with worksheet scope. The document also contains an overview worksheet that sums up values from named cells across the worksheets. The clearest way to achieve this is to refer to these cells via their names. In Excel this is perfectly possible: the reference syntax is to combine the sheet name, an exclamation mark, and the name defined in that worksheet. Creating such a file in Excel and then opening it in LibreOffice will produce #REF errors.

Solution: make the formula engine treat references of the form sheet!name (where "name" is not a cell or range reference in the currently active [A1 or R1C1] syntax) to resolve the name "name" as it would do in the worksheet "sheet", even if the reference appears in a different worksheeet.
Comment 1 Buovjaga 2016-01-06 19:13:48 UTC
Reading this: https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=75806
Did you try defining your name in absolute terms, like in the forum topic: Sched_D_Line16=$Schedule_D.$F$24
Schedule_D being the name of the worksheet.

Set to NEEDINFO.
Comment 2 sebalis 2016-01-07 02:59:02 UTC
Created attachment 121760 [details]
File with a local name "name1" defined in Sheet1

I don't think that it matters whether the name is defined using absolute or relative references. What matters for this bug is that the name’s scope is not global but within one worksheet. See the attached file. In Sheet1, cell $A$1 is named “name1”. Now go to Sheet2 and try to refer to that cell in Sheet1 not by its “coordinates” but by its name.
Comment 3 Buovjaga 2016-01-07 08:11:22 UTC
When managing names, I can set name1 to have Scope: Document (Global).
So what exactly is the bug?
Comment 4 sebalis 2016-01-07 12:35:57 UTC
It’s described in my first comment: there are situations when a) it would not be right to change the scope to global and b) it is still legitimate and necessary to refer to the name from another sheet. And c) Excel has this capability so LibreOffice can’t open Excel files that use it, which could easily be rectified. (But I didn’t submit this bug because I want to be able to open my favourite Excel file; I want to work in LibreOffice and use this perfectly reasonable capability there.)
Comment 5 sebalis 2016-01-07 12:37:01 UTC
(You could at least set this bug to something other than “unconfirmed” – it’s very easy to confirm that the problem exists.)
Comment 6 Buovjaga 2016-01-07 12:58:28 UTC
Ok, sorry about my confusion :)
Comment 7 Eike Rathke 2016-03-14 11:51:15 UTC
*** Bug 98643 has been marked as a duplicate of this bug. ***
Comment 8 Eike Rathke 2016-03-16 18:54:15 UTC
I'm at it.
Comment 9 Commit Notification 2016-03-18 09:39:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=64e542413851236c75e25185c137d6fd6ddfe3a1

Resolves: tdf#96915 implement other-sheet-local named expressions

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.
Comment 10 Commit Notification 2016-03-18 11:32:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=090de0e963fd3b0c7c4f8db4124b71b0fd92f61f

let FindRangeNamesInUse() collect also sheet-local names, tdf#96915 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.
Comment 11 Commit Notification 2016-03-18 13:38:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=cf56d2449c27130ae40dc940ddcdb13de1128b7f

adjust other-sheet-local relative sheet references, tdf#96915

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.
Comment 12 Commit Notification 2016-03-18 15:38:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=51bd0c6b8f7bcaabecbda467285d6ffb9a73382b

unit test for other-sheet-local names, tdf#96915

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.
Comment 13 Commit Notification 2016-03-22 13:04:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=97269062ca08d4265e15eb0e8343f90f10810403

enquote sheet name of other-sheet-local named expressions, tdf#96915 follow-up

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.
Comment 14 Eike Rathke 2016-03-22 13:15:40 UTC
Let's call this implemented.
Comment 15 Eike Rathke 2016-04-12 15:18:46 UTC
*** Bug 92912 has been marked as a duplicate of this bug. ***
Comment 16 royerjy 2016-06-26 07:33:13 UTC
Created attachment 125909 [details]
In sheet copy global name is in fact local name

With 5.2.0 Build ID: 91f2f71e7b936c3c9fb984aaa01d432926abb38f when copying a sheet "global name" is in fact "local name". See example.

Sorting names list should probably be : sheet name primary et field name secondary. Global names first ?

After copying a sheet, it is necessary to refresh list names in tool bar. Actually, it is necessary to save le spreadsheet and load it to obtain the correct list names.
Comment 17 sebalis 2016-07-07 23:20:34 UTC
Thank you very much for developing this fix. However, it is only partial. (Sorry for not being able to test this earlier.) I attach a file “local-name-test.ods” created with LibreOffice 5.2.0.1. Cell R1C1 in Sheet1 contains a direct reference to a name that is defined in Sheet2 with local scope: =Sheet2!pi_value. This works now – it did not work in LibreOffice 5.1 so this is where the fix can be observed. However, cell R2C1 in Sheet1 contains the same reference expression as a string parameter to the INDIRECT function – =INDIRECT("Sheet2!pi_value") –, and here it does not work: the cell shows the #REF! error. Adding a second parameter to INDIRECT does not change this (as you probable know, the parameter differentiates between A1 and R1C1 notation, so it is of course not required here as the reference is to a name).

I’m surprised that the fix is not effective in the way the INDIRECT function resolves references. Can I kindly ask for the fix to be extended to this case, and could you consider where else there might be reference-resolving code that also might need to be changed?

Again, thanks for taking this issue on at all.
Comment 18 sebalis 2016-07-07 23:21:44 UTC
Created attachment 126108 [details]
test case as described in comment #17
Comment 19 Eike Rathke 2016-07-08 18:11:08 UTC
(In reply to royerjy from comment #16)
> In sheet copy global name is in fact local name

It has to be. See https://wiki.documentfoundation.org/ReleaseNotes/5.2#Copying_named_expressions_and_ranges_while_copying_sheets

> Sorting names list should probably be : sheet name primary et field name
> secondary. Global names first ?

You can sort as you like by clicking on the Name or Expression or Scope headers.

> After copying a sheet, it is necessary to refresh list names in tool bar.
> Actually, it is necessary to save le spreadsheet and load it to obtain the
> correct list names.

What toolbar are you talking about?


(In reply to sebalis from comment #17)
> However, cell R2C1 in Sheet1
> contains the same reference expression as a string parameter to the INDIRECT
> function – =INDIRECT("Sheet2!pi_value") –, and here it does not work:

Please submit a separate bug for the INDIRECT failure, thank you.
Comment 20 sebalis 2016-07-09 00:26:24 UTC
Thanks for for the advice. I have now opened bug #100818.
Comment 21 royerjy 2016-07-09 06:36:51 UTC
(In reply to Eike Rathke from comment #19)
> 
> What toolbar are you talking about?
> 
It is "Formula bar" in 5.2.0.0+