Apache OpenOffice (AOO) Bugzilla – Issue 3740
Named ranges in spreadsheet cannot be accessed from another one
Last modified: 2023-04-29 09:47:45 UTC
In Excel 97 I create two spreadhseets: In "A" I have a coloumn of numbers, and I assign the name 'test' to the range. In "B" I use a sum() referring to the file A and its named range test. This works properly. If I look at those files in OpenOffice, the reference to the named range in file "B" is lost, and a name error occurs, ie, the conversion goes wrong. I cannot set it manually either; if I select the range in "A", it does not recognize it as a named range. I have put the two files onto http://www.euronet.nl/users/iherman/A.xls and http://www.euronet.nl/users/iherman/B.xls
OOo Calc does not support external named ranges, so the Excel import filter has to procuce an error.
I change this item from defect to enhancement. Maybe it is possible to implement this feature
.
FT->NN: This is an import/export issue. Please verify and retarget it (if necessary).
This is not planned for 2.0.
*** Issue 44292 has been marked as a duplicate of this issue. ***
*** Issue 45705 has been marked as a duplicate of this issue. ***
My issue number 44292 was closed as a duplicate of this issue, and similarly a more recent issue number 45705 was also closed as a double. This is NOT just an import/export issue as one responder said. Sure, it comes up as a major problem loading spreadsheets from excel into OpenOffice and basically makes such importing impossible from my perspective, since OpenOffice Calc fails to support properly such a basic function. But even ignoring the excel import issue and considering writing wholly new spreadsheets in Calc without any intention of exporting them it is still a basic need to reference other Calc files, and using named ranges to do this solves many problems which can occur when the referenced file is updated. i.e. when a referenced file is changed, the location of a specific cell may move, say if you have added a new row, but by referencing it by cell number in the second sheet, you will lose the link. However, if you have referenced by name, then it doesn't matter how the cell is moved around, it will still be referenced correctly. There does seem to be a messy work around in OpenOffice using DDE, as I noted in my issue number 44292, which included example attacments. But it would be preferable if Calc performed this function properly. To my mind this is a basic failing in Calc which makes it unusable in my business, not just a nice enhancement. If the funtion works when using DDE, then why cannot the same code be used to make it work for normal referencing or when importing/exporting? Please consider introducing the correct functionality as soon as possible. regards.
IMPORTANCE OF THIS ISSUE ======================== There are situations when users need to access data inside a complex spreadsheet (e.g. a master spreadsheet stores uniquely some basic data, and multiple users in various departments need this data in their work). There are 2 ways to do this: * using DDE-links between the 2 spreadsheets * using a direct reference to the data inside one spreadsheet [aka writing a formula to reference this external data] [Please note that the first mechanism does NOT currently work in OOo IF the data range contains formulas! Therefore, users will be forced to use the 2nd mechanism.] Currently, OOo Calc users will need to reference directly the data inside the master-spreadsheet, because the external reference mechanism does NOT support *named ranges*. Why are *named ranges* such important? A.) CORRECT RANGE ================= Well, IF the user wrote the master-spreadsheet he may be able to recall in which rows/columns he did store the relevant data. BUT how well will he remember these design details in 2 weeks? And in 2 month? And what are the chances, that a different user will reference the correct range? B.) CHANGING RANGE ================== Another even worse problem arises when the initial data range changes: say, one has to add another data row, so *ALL END USERS* will have to correct their spreadsheets that link to this master-spreadsheet. This is a formidable task and one that generates numerous *hard-to-trace errors*. These are the main reasons, why it is desirable to link to a named range! To give some better perspective on this issue, lets consider by analogy the C++ classes: classes have both private and public methods. End users are however able to use only the public methods. These hide the implementation details of the private methods. IF something changes internally, the external user should not be affected. It is the same as the OOo UNO-copmonents. This is what named ranges are good for. I hope that my explanation will foster handling named ranges in the external reference code. ---- added myself to cc
I'm taking ownership.
accepting the issue.
Issue 4385 is reserved specifically for the Excel import issue.
Let's target this for 3.1.
Ah, maybe not. 3.x that is.
turns out that we have to aim for the 3.1 integration.
Added this issue to cws mooxlsc.
fixed in mooxlsc cws. All issues raised during development have been resolved, so I think it's reasonable to call this "fixed".
I have just downloaded OpenOffice 3.0.0 which I assumed would have this issue fixed and it is certainly not fixed. OpenOffice does not work with Excel files using named ranges referencing another file. There seems to be no difference in its behaviour in version 3 from version 2. However named ranges within a spreadsheet seem to be accessible. Whist checking this I also found that most of the DDE links I use do not work though encouragingly when opening a spreadsheet with DDE links it said that the file contained links to another file and asked if I wanted them updated. I responded Yes but unfortunately they did not update. However DDe links from one source did update so it could be a non standard usage in the other program but either way it will prevent me migrating to Open Office. The failed DDE links look like this if I have not updated links =DDE("TRADER";"LSE\INDICES";"UKX\mid") but return the previous value and look the same if I have updated links but return a blank, as opposed to zero ( I have view zero switched on) . Also although in Options I have update links set to on request at least one spreadsheet automatically updates links to another spreadsheet resulting in name errors so I cannot show the link format, However one spreadsheet correctly asks if I want them updated and if I say no the link format is shown as follows: ='file:///C:/My Documents/Excel files/OPTVAL.xls'#$A.$D$9 I think what distinguishes the spreadsheet that asks about updating links form the one that does not is the presence of DDE links in it. With spreadsheet only links it goes straight to update. If I update links the result looks like this Err:508 The same happens whether it is a named range or a cell reference like this. Dou you have to convert all files to Calc files for links to work or create new links from within OpenOffice. I tried this having saved both files in ods format and following the help instructions using the navigator to create a link and got a message that the link could not be updated so I will give up on OpenOffice again. I have been linking spreadsheets since Supercalc 30 years ago so this is disappointing.
@fabianfinlay: Note the target of this issue: OOo3.1 The implementation is in a CWS not yet integrated. You may track the status of the CWS at http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fmooxlsc For more information on CWSs see http://wiki.services.openoffice.org/wiki/ChildWorkSpace And please don't mix in other issues. Query the issue tracker if your problem about non-updating links was already reported, and if not file an issue and attach a set of test case documents to reproduce. Thanks.
It is sad that this issue continues to drag on. I first commented in March 2005, when they were still developing version 2.0beta. I even gave a bunch of examples and suggestions (Issue number 44292). But then my issue number was cancelled because it duplicated this even earlier one which goes way back to 2002! Now here we are at version 3.0 and still no progress. Maybe it will appear in 3.1, but I wouldn't bet on it. As I've said before, this is a major stumbling block that stops many people from being able to use Calc at all. Even Excel 2.0 from 1987 could do something this simple! And while I can't afford to buy the latest Microsoft crap, my Excel 97 still works fine.
I made a mistake when I said I had been doing this from the days of Supercalc. Yes I had been doing it with Supercalc, Lotus and then Excel but I started linking spreadsheets as long ago as Visicalc though I have to say that back then I don't think it was as easy as using named ranges. Remember that program. Program and data files would fit on a 64k floppy disk and to build anything big you had to link them. Sorry I mixed issues. They seem largely related to me as to functionality but I will try and do better next time but will wait and see how the fix on named ranges works before I test the other aspects.
Reopening. @kohei: Though external references within Calc now work in cws mooxlsc, the submitter's test case document http://www.euronet.nl/users/iherman/B.xls does still produce a #NAME error. Apparently the name does exist, ScExternalRefCache::getRangeNameTokens() finds it, but returns a token array that contains one token of type svByte with OpCode ocBad.
Well, updating the link does fix it, so it's a matter of importing the cached name range correctly. Still looking into it.
Well, the reason was simple; I forgot to parse range references in the cached external range records in xls. Adding a handler for that fixed the issue. OTOH, I swear I thought I had covered this. Well I guess this simply slipped my mind... :-/
fixed again.
kohei, Does this mean there is a version we can download now and try out for this issue. fabian
@fabianfinley: No, no yet. The only way to test this feature is to download the source from the cws and build it yourself. The sources can be downloaded by svn co svn://svn@svn.services.openoffice.org/ooo/cws/mooxlsc (I think this is right...)
Sigh.. I have to reopen this again. The external defined name references currently are not stored in ODF. This has to be specified first. Due to the tight time schedule that would result in the entire CWS mooxlsc not being ready before OOo3.1 feature freeze. Instead, I remove this issue from the CWS and retarget it to OOo3.2 for a follow-up CWS; the code will remain in place, but the named reference will not be resolved.
set to invalid
Why on earth is this invalid?
*** Issue 105700 has been marked as a duplicate of this issue. ***
OOo 3.2 is in show-stopper stage. This issue is re-targeted to OOo 3.x. If this issue is critical for the current release please target it back.
> The external defined name references currently are not stored in ODF. > This has to be specified first. @er: Are there currently any specification efforts on this? If so, can you place some comments on the progress here ? (or maybe in another ticket)
So... Any ideas about when will we see this fixed?
I saw today there is already a beta version for OOv3.3. Does it happen to store the names in the ODF alerady? Is there annother issue where we can track the specification/implementation progress for storing external defined name references in the ODF?
Reset assignee on issues not touched by assignee in more than 1000 days.
*** Issue 120820 has been marked as a duplicate of this issue. ***
Sadly, this patch (CWS) seems to be lost in limbo. Could someone track it down and suggest a PR of it on Github?
Unfortunately this is not a single patch but a branch (mooxlsc) based on OOo 3.1. It would need a lot of work to apply the changes to AOO now. That said, it is not impossible.