Bug 92939 - RangeName conversion from Excel 5.0 to CALC - RangeNames local to specific sheets get attached to the wrong sheet
Summary: RangeName conversion from Excel 5.0 to CALC - RangeNames local to specific sh...
Status: RESOLVED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.4.3 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-07-26 11:19 UTC by Alex
Modified: 2018-03-07 15:47 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel .xls file and the Calc .ods file converted from it (18.58 KB, application/zip)
2015-07-26 11:19 UTC, Alex
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex 2015-07-26 11:19:33 UTC
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?".
Comment 1 m_a_riosv 2015-07-26 22:17:16 UTC
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 ***
Comment 2 Alex 2015-07-27 01:28:02 UTC
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!
Comment 3 Alex 2015-07-27 02:02:57 UTC
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.
Comment 4 Alex 2015-08-11 05:13:07 UTC
Just reporting that the reported behavior/bug remains unfixed on version 5.0.0.5
Comment 5 Buovjaga 2015-10-09 18:27:53 UTC
Bug does not meet the criteria for Status 'REOPENED'
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/REOPENED#Criteria
Status -> UNCONFIRMED
Comment 6 Joel Madero 2015-10-10 16:53:27 UTC
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.
Comment 7 Alex 2015-10-11 06:58:56 UTC
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.
Comment 8 Joel Madero 2015-10-11 17:31:36 UTC
I'll put it back in UNCONFIRMED and see if someone else can make sense of it. If not we can close as INVALID
Comment 9 Alex 2015-10-11 18:26:47 UTC
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.
Comment 10 Joel Madero 2015-10-11 20:07:49 UTC
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.