Issue 3740 - Named ranges in spreadsheet cannot be accessed from another one
Summary: Named ranges in spreadsheet cannot be accessed from another one
Status: REOPENED
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: DEV300m25
Hardware: PC All
: P2 Trivial with 8 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: ms_interoperability
: 44292 45705 (view as issue list)
Depends on:
Blocks: 4385 80139
  Show dependency tree
 
Reported: 2002-03-30 14:18 UTC by iherman
Modified: 2013-08-07 15:13 UTC (History)
5 users (show)

See Also:
Issue Type: ENHANCEMENT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description iherman 2002-03-30 14:18:07 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
Comment 1 daniel.rentz 2002-04-09 08:51:43 UTC
OOo Calc does not support external named ranges, so the Excel import filter has to 
procuce an error.
Comment 2 oc 2002-05-16 11:37:22 UTC
I change this item from defect to enhancement. Maybe it is possible 
to implement this feature
Comment 3 oc 2002-05-16 11:37:48 UTC
.
Comment 4 falko.tesch 2003-09-30 15:04:29 UTC
FT->NN: This is an import/export issue. Please verify and retarget it
(if necessary).
Comment 5 niklas.nebel 2003-10-02 18:56:33 UTC
This is not planned for 2.0.
Comment 6 frank 2005-03-24 13:02:03 UTC
*** Issue 44292 has been marked as a duplicate of this issue. ***
Comment 7 frank 2005-04-07 11:27:25 UTC
*** Issue 45705 has been marked as a duplicate of this issue. ***
Comment 8 gunamoi 2005-09-07 02:25:43 UTC
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.
Comment 9 discoleo 2007-09-16 15:02:15 UTC
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
Comment 10 kyoshida 2008-07-02 14:44:11 UTC
I'm taking ownership.
Comment 11 kyoshida 2008-07-02 14:44:53 UTC
accepting the issue.
Comment 12 kyoshida 2008-07-02 14:46:10 UTC
Issue 4385 is reserved specifically for the Excel import issue.
Comment 13 kyoshida 2008-07-17 02:36:43 UTC
Let's target this for 3.1.
Comment 14 kyoshida 2008-07-17 02:38:19 UTC
Ah, maybe not.  3.x that is.
Comment 15 kyoshida 2008-08-14 15:40:48 UTC
turns out that we have to aim for the 3.1 integration.
Comment 16 ooo 2008-08-20 16:02:33 UTC
Added this issue to cws mooxlsc.
Comment 17 kyoshida 2008-09-26 04:37:10 UTC
fixed in mooxlsc cws.  All issues raised during development have been resolved,
so I think it's reasonable to call this "fixed".
Comment 18 fabianfinlay 2008-10-22 08:03:58 UTC
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.

Comment 19 ooo 2008-10-22 11:00:26 UTC
@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.
Comment 20 gunamoi 2008-10-22 12:41:27 UTC
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.
Comment 21 fabianfinlay 2008-10-22 19:56:33 UTC
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.
Comment 22 ooo 2008-11-21 00:29:29 UTC
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.
Comment 23 kyoshida 2008-11-21 16:31:25 UTC
Well, updating the link does fix it, so it's a matter of importing the cached
name range correctly.

Still looking into it.
Comment 24 kyoshida 2008-11-21 17:51:28 UTC
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... :-/
Comment 25 kyoshida 2008-11-21 20:35:48 UTC
fixed again.
Comment 26 fabianfinlay 2008-11-24 05:57:20 UTC
kohei,

Does this mean there is a version we can download now and try out for this issue.

fabian
Comment 27 kyoshida 2008-11-24 13:57:16 UTC
@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...)
Comment 28 ooo 2008-12-02 15:12:57 UTC
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.
Comment 29 wope 2009-07-03 02:05:42 UTC
set to invalid
Comment 30 kyoshida 2009-07-03 02:52:00 UTC
Why on earth is this invalid?
Comment 31 kyoshida 2009-10-07 19:54:13 UTC
*** Issue 105700 has been marked as a duplicate of this issue. ***
Comment 32 thorsten.ziehm 2009-11-04 13:52:21 UTC
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.
Comment 33 jjschulz 2009-11-04 14:48:04 UTC
> 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)
Comment 34 elysch 2010-02-04 00:08:45 UTC
So... Any ideas about when will we see this fixed?
Comment 35 elysch 2010-09-22 17:34:11 UTC
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?

Comment 36 Rob Weir 2013-07-30 02:38:20 UTC
Reset assignee on issues not touched by assignee in more than 1000 days.