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.
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.
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.
When managing names, I can set name1 to have Scope: Document (Global). So what exactly is the bug?
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.)
(You could at least set this bug to something other than “unconfirmed” – it’s very easy to confirm that the problem exists.)
Ok, sorry about my confusion :)
*** Bug 98643 has been marked as a duplicate of this bug. ***
I'm at it.
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.
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.
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.
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.
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.
Let's call this implemented.
*** Bug 92912 has been marked as a duplicate of this bug. ***
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.
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.
Created attachment 126108 [details] test case as described in comment #17
(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.
Thanks for for the advice. I have now opened bug #100818.
(In reply to Eike Rathke from comment #19) > > What toolbar are you talking about? > It is "Formula bar" in 5.2.0.0+