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?
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.
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!
Excel supported cross-sheet named references in version 5.0, released around 1995. That's two decades.
Excel supported cross-sheet named ranges as well as cross-file named ranges in version 5.0, released around 1995.
*** This bug has been marked as a duplicate of bug 96915 ***