Bug 92912 - EDITING: Allow referencing a named range in another sheet
Summary: EDITING: Allow referencing a named range in another sheet
Status: CLOSED DUPLICATE of bug 96915
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
Depends on:
Reported: 2015-07-24 10:58 UTC by Marcus Bointon
Modified: 2016-04-12 15:18 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Demonstrate cross-sheet named references not working (7.14 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-07-24 10:58 UTC, Marcus Bointon

Note You need to log in before you can comment on or make changes to this bug.
Description Marcus Bointon 2015-07-24 10:58:48 UTC
Created attachment 117416 [details]
Demonstrate cross-sheet named references not working

This bug is mentioned in https://bugs.documentfoundation.org/show_bug.cgi?id=82135, and I'm posting it as a separate report as requested.

Imported Excel 2007 spreadsheets that contains a formula that refers to a named range on another sheet of the same workbook display a zero value or #NAME? error.

In the attached Excel file, cell A1 on sheet2 contains the value 123. That cell is named 'namedthing' and is listed in the named ranges manager. Cell A1 on sheet1 contains a formula that refers to it: "='sheet2'!namedthing". On opening the file, instead of showing the referenced value, it shows a 0 (which may be a symptom of the above linked bug). If I edit the formula, delete the last char and retype it (i.e. not changing it), it changes to a #NAME? error.

If I choose insert -> names -> insert... I can see it defines the name, including which sheet it is defined on, but selecting that inserts only an unqualified name reference (just "namedthing") which does not resolve on sheet1.

I don't know if this is specific to Excel imports, but I couldn't find any working syntax for cross-sheet named references, for example while "=$sheet1.A1" works, "=$sheet1.namedthing" does not. I also note that the Excel import does not convert references from single-quotes and ! to $ and . syntax.

I assume this is supposed to work?
Comment 1 m.a.riosv 2015-07-24 13:49:18 UTC
Hi Macus, thansk for reporting.

As Marcus (dev) write in https://bugs.documentfoundation.org/show_bug.cgi?id=82135#c4
"We don't support to address sheet local names through the 'sheet name".rangename syntax."

What is the case in your sample file. ='sheet2'!namedthing, changing the separator from ! to . is not a solution either.

Maybe will be better change for a request of enhancement, in the importance. Adapting the title to it.

Please if you are not agree, reopen the report.
Comment 2 Marcus Bointon 2015-07-24 15:32:47 UTC
I'm reopening this as an enhancement request. The ability to refer to cross-sheet named ranges has been in Excel since at least 2007, possibly since 2002. Aside from the Excel compatibility aspect, it's extremely useful for building report-type spreadsheets that gather information from multiple similar sheets. It seems odd that Calc supports cross-file named ranges (a far more complex thing) but not this. As it stands it's like trying to use a programming language with no variables!
Comment 3 Alex 2015-10-11 07:06:40 UTC
Excel supported cross-sheet named references in version 5.0, released around 1995.  That's two decades.
Comment 4 Alex 2015-10-11 07:11:10 UTC
Excel supported cross-sheet named ranges as well as cross-file named ranges in version 5.0, released around 1995.
Comment 5 Eike Rathke 2016-04-12 15:18:46 UTC

*** This bug has been marked as a duplicate of bug 96915 ***