Created attachment 117446 [details] Excel .xls file and the Calc .ods file converted from it RangeNames local to specific sheets get attached to the wrong sheet during the conversion process, with the result that formula references to those names can't be resolved by the conversion process and get replaced in the formulas with "#NAME?".
Hi Ales, thanks for reporting. As Marcus (dev) has written 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." Enhancemente requested in: https://bugs.documentfoundation.org/show_bug.cgi?id=92912 *** This bug has been marked as a duplicate of bug 82135 ***
I had seen the reference to bug 82135 and the other reference before reporting this bug, and I do not consider them the same. Yes, I (unfortunately) know all too well of the inability of LibreOffice Calc to reference names local to Sheet1 in a formula on Sheet2. But, Calc (at last!) now does allow names local to a sheet to be referenced from formulas on that sheet. The bug I reported and demonstrated in the attachments was that in the conversion from Excel 5.0, the names get attached to the wrong sheets - i.e., a name local to Sheet1 got attached to Sheet3 and could therefore no longer be accessed from Sheet1. Perhaps this behavior is the underlying cause of the other bug(s) you wanted to call identical to this, but the details are not what were reported in the other bugs I found. While I might complain about the deficiencies in the way LibreOffice Calc handles (or doesn't handle) foreign references to local names, that is not what I reported as a bug!
After some subsequent investigation I can report that the order in which sheets appear in the Excel workbook has some effect on whether local names get attached to the correct sheet. If, for example, in the attachments I sent in the original bug report, one moves SHEET4 and SHEET3 to the end in the Excel workbook, so that the tab order is SHEET2, SHEET1, SHEET4 and SHEET3, when that is then opened by Calc the names local to SHEET1 and SHEET2 get attached correctly. It seems that if there are sheets in the document which do not have any local names that are to the left (in the tab order) of those which do, the local names on those which do get shifted as many sheets to the left as there are preceeding sheets without local names. It's hard for me to imagine why this type of behavior should happen, inasmuch as it shouldn't be too difficult to look at an Excel name and easily figure out whether it is global or local, and if local, what sheet it belongs to when the name is converted to Calc. Something is not right in that process.
Just reporting that the reported behavior/bug remains unfixed on version 5.0.0.5
Bug does not meet the criteria for Status 'REOPENED' https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/REOPENED#Criteria Status -> UNCONFIRMED
To begin with - comments like "at last!" are entirely unhelpful and demotivating - please stick to the issue instead of implying that "it's about damn time" when you're not the one doing the work. Once you offer tens of thousands of hours of time at no cost, you can complain (or imply to complain) that it took too long. Outside of that. I am throwing this back to NEEDINFO - the paragraphs of text are not easy to follow. Please just describe the issue in enumerated steps. Provide a SIMPLE test case and direct us EXACTLY to where the problem is. When I compare the xls to the ods they look the same on a quick glance. Describing the problem in the spreadsheet is not sufficient - we need the clear (not paragraph) explanation in the bug report. After doing this please set to UNCONFIRMED.
I can't provide a better explanation than I did. You can delete this bug submittal if you wish. I would withdraw it myself, but I don't see a way to do so.
I'll put it back in UNCONFIRMED and see if someone else can make sense of it. If not we can close as INVALID
I think in the overall scope of things this bug doesn't warrant additional attention. First of all, it may be that it only applies to Excel 5.0 formats, and there are probably very few other people still using a 20 year-old version of Excel. But, even if this bug weren't there, someone with an Excel workbook of any version which used NamedRanges who tries to convert it to Calc would soon find that Calc doesn't handle NamedRanges with the full range of features that Excel did 20 years ago, and be faced with either staying with Excel or re-designing their workbook in order to deal with the relative defficiencies of Calc. I attempted the latter, gave up, and stayed with Excel for workbooks employing NamedRanges, Basic programming extensions, or custom dialogs.
Given the final comment I suppose I'll close this. Thanks Note I'm putting as INVALID just because there isn't a status that quite addresses the status of this bug.