Issue 4904 - ODFF: Dynamic ranges of named cells, OpCode ocRange
Summary: ODFF: Dynamic ranges of named cells, OpCode ocRange
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.0.0
Hardware: All All
: P3 Trivial with 13 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: ms_interoperability
: 5052 11799 20587 26004 32483 37351 38413 42678 43804 46210 47976 48761 51186 53262 53263 55662 55758 56051 56936 57341 72174 81077 94406 (view as issue list)
Depends on:
Blocks: 57108
  Show dependency tree
Reported: 2002-05-15 15:02 UTC by empii
Modified: 2013-08-07 15:15 UTC (History)
4 users (show)

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

test cases (8.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-11-29 19:21 UTC, ooo
no flags Details
more test cases involving two sheets (9.30 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-12-05 12:03 UTC, ooo
no flags Details
corrected test cases; in the previous attachment not all were semantically correct (9.55 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-12-05 22:20 UTC, ooo
no flags Details
TestCaseSpecification (6.61 KB, text/html)
2008-02-26 12:33 UTC, oc
no flags Details
Testdocuments for Test Case Specification (10.08 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-02-26 12:34 UTC, oc
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description empii 2002-05-15 15:02:04 UTC
Cell C9 has name defined "test1"
Cells c10,c11 contain 1(numeric value)
cell d9 contains formula =SUM(test1:c11)

Cell d9 results "#NAME?"

Problem arised when tried to open Excel's file where this function works just 
Comment 1 josh 2002-05-15 19:05:47 UTC
Tested on 1.0.0 Win98 and 1.0.0 Linux. 
Neither the SUM nor the COUNT functions respect references 
to named cells.  That is, 
will always result in a #NAME? error. 
-Josh Berkus 
Comment 2 oc 2002-05-16 12:31:53 UTC
Names as parameters in areas are not supported by OOo => Enhancement
Comment 3 prgmgr 2002-07-26 04:12:18 UTC
*** Issue 5052 has been marked as a duplicate of this issue. ***
Comment 4 daniel.rentz 2003-02-25 10:16:26 UTC
*** Issue 11799 has been marked as a duplicate of this issue. ***
Comment 5 falko.tesch 2003-10-01 11:01:45 UTC
Support names (of cells) as parameters in areas.
Note: This is also an Excel related issue
Comment 6 falko.tesch 2003-10-01 11:01:58 UTC
Comment 7 daniel.rentz 2003-10-08 12:17:03 UTC
*** Issue 20587 has been marked as a duplicate of this issue. ***
Comment 8 ooo 2003-10-08 12:27:02 UTC
The problem is not the use of named cells (we do support that) but
constructing a range of named cells used as "corners". Grabbing issue,
as the range operator is on my list for interoperability enhancements
for OOo2.0
Comment 9 ooo 2003-10-08 12:27:47 UTC
Comment 10 ooo 2003-10-08 12:28:27 UTC
reassigning I said..
Comment 11 ooo 2003-10-08 12:29:00 UTC
Comment 12 john.marmion 2004-03-04 10:02:02 UTC
*** Issue 26004 has been marked as a duplicate of this issue. ***
Comment 13 ooo 2004-05-27 11:57:07 UTC
Add blocking dependency to issue 20494, raise priority to P3.
Comment 14 Martin Hollmichel 2004-05-28 15:01:15 UTC
according to the announcement on releases
( this issue
will be re-targeted to OOo Later.
Comment 15 ooo 2004-06-01 10:50:39 UTC
Sigh.. seems the list of issues to be retargeted was created  before I changed
priority of this one.. so back to OOo2.0.
Comment 16 ooo 2004-07-30 11:29:38 UTC
Sigh again.. product management decided to postpone remaining features from
OOo2.0 to OOoLater.
Comment 17 frank 2004-08-03 09:57:45 UTC
*** Issue 32483 has been marked as a duplicate of this issue. ***
Comment 18 dankegel 2004-08-03 15:59:37 UTC
Since this issue kept me from being able to read a trivial
family finance spreadsheet my wife wrote in Excel '97,
I'm adding the ms_interoperability keyword, changing
the type to DEFECT, and targeting it back at OOo 2.0.
The ball's back in your court :-)
Comment 19 ooo 2004-08-03 16:40:36 UTC
Please don't mess around with the targets. Thank you.
And please don't mess around with the type of issues either. Thank you.
This is a feature that has to be implemented, with all consequences, even if it
looks like a defect to you. Adding the ms_interoperability keyword was ok.
Reverting target and type to what they were before.
Comment 20 frank 2004-08-03 16:44:22 UTC
So you're willing to implement it with all such problems that arise from this ?!

I don't think so.

This is a feature request, time and resources to fix it for OOo2.0 are not
available and so this has to stay as OOo later.

Also this kind of doing sum's is not a common one, you have to live with that.
Simply erase the :a2 from the formula and you get a correct result.

Comment 21 dankegel 2004-08-06 06:49:33 UTC
I can buy it being delayed until after 2.0,
am mildly surprised the ms_interoperability keyword
was removed.  Seems like a bit of denial might
be in play here.

I only because interested in this
when I was unable to load a trivial spreadsheet
my wife wrote and sent me (see issue 32483).
I don't have any evidence that the problem
from her spreadsheet occurs often -- maybe
she just typed something strange, and it happens
to work in MS Office and Gnumeric, but not OOo --
but if I run into it in the wild again, I'll
come back and re-propose the ms_interoperability keyword.
Comment 22 dankegel 2004-08-06 06:51:48 UTC
BTW I'm not convinced issue 32483 is
really a dupe of this issue.  32483 doesn't
involve dynamic ranges.  Maybe my bad spreadsheet
is easier to fix than this dynamic range issue.
Comment 23 ooo 2004-08-06 07:57:10 UTC

I also don't know why Frank removed the ms_interoperability keyword, this is a
clear interoperability issue, and so I will add the keyword again. We had to
postpone this feature (retarget to OOoLater) because we don't have the resources
to implement it in the time line for OOo2.0.

And yes, issue 32483 really is a dupe of this. Although it doesn't involve
dynamic ranges with named cells, the expression a1:a2:a3 results in a range
a1:a3 by having the second ':' range operator extend the range a1:a2 to a1:a3.
Just a special case of range operator use.

Btw: in normal formula input mode, Excel doesn't even allow such range to be
entered, it doesn't let you add another ':' after the already exisintg a1:a2,
only when you switch the cell input mode to edit mode (by pressing F2) it lets
you do such things.

Comment 24 daniel.rentz 2004-11-18 10:06:42 UTC
*** Issue 37351 has been marked as a duplicate of this issue. ***
Comment 25 frank 2004-12-08 14:39:16 UTC
*** Issue 38413 has been marked as a duplicate of this issue. ***
Comment 26 frank 2005-02-14 08:52:36 UTC
*** Issue 42678 has been marked as a duplicate of this issue. ***
Comment 27 frank 2005-03-07 11:36:48 UTC
*** Issue 43804 has been marked as a duplicate of this issue. ***
Comment 28 tulitanssi 2005-03-09 16:24:08 UTC
This problem has been around for almost three years, which is a very long time.
When will it be fixed?
Comment 29 frank 2005-04-14 14:27:13 UTC
*** Issue 46210 has been marked as a duplicate of this issue. ***
Comment 30 dankegel 2005-04-14 15:02:13 UTC
This is starting to sound like it's
really going to be an issue for people
switching from Excel.   My wife ran into
it, consultants are running into it,
it's biting more people than Sun seems
to have expected.  IMHO this merits 
being a DEFECT rather than an enhancement,
and it deserves a target more definite than 
OOo Later.  Can you change it to DEFECT and
set the target to something like OOo 2.1, please?
Comment 31 daniel.rentz 2005-04-28 17:08:02 UTC
*** Issue 47976 has been marked as a duplicate of this issue. ***
Comment 32 ooo 2005-05-02 14:51:25 UTC

The range operator is not implemented at all, therefor this is a FEATURE, and
not a DEFECT. A defect is something not working as it was designed. How you name
it does not influence in what time frame it gets handled. The order of
importance what feature gets implemented for which version (e.g. if for 2.1 or
3.0 or what ever we may call them, we don't even have targets yet) is determined
by the program management. However, note that this is quite on top of the
features-to-implement list now,  see also issue 20494 that was used  to list
some of them.

Comment 33 daniel.rentz 2005-05-09 17:45:07 UTC
*** Issue 48761 has been marked as a duplicate of this issue. ***
Comment 34 daniel.rentz 2005-07-05 16:50:30 UTC
*** Issue 51186 has been marked as a duplicate of this issue. ***
Comment 35 frank 2005-09-13 10:19:01 UTC
*** Issue 53262 has been marked as a duplicate of this issue. ***
Comment 36 frank 2005-09-13 10:19:17 UTC
*** Issue 53263 has been marked as a duplicate of this issue. ***
Comment 37 Regina Henschel 2005-10-08 21:02:05 UTC
*** Issue 55662 has been marked as a duplicate of this issue. ***
Comment 38 frank 2005-10-11 11:48:08 UTC
*** Issue 55758 has been marked as a duplicate of this issue. ***
Comment 39 Regina Henschel 2005-10-15 13:47:39 UTC
*** Issue 56051 has been marked as a duplicate of this issue. ***
Comment 40 Regina Henschel 2005-10-30 00:07:23 UTC
*** Issue 56936 has been marked as a duplicate of this issue. ***
Comment 41 timdeaton 2005-11-01 02:35:12 UTC
Has there been any progress on this issue?  Any idea yet when a resolution is
likely to see the light of day?
Comment 42 Regina Henschel 2005-11-04 10:15:17 UTC
*** Issue 57341 has been marked as a duplicate of this issue. ***
Comment 43 tulitanssi 2006-01-14 07:37:27 UTC
This item truly shows the weakness of Open Source.
Comment 44 timdeaton 2006-01-15 03:26:11 UTC
Perhaps this is unnecessary effort, but I spent tonite looking back at all the
other issues closed into this one, in order to understand the problem.  It
helped.  With 22 other issues (so far) combined into this one, I decided it
might help generate movement on this if all of that history was summarized below:

Issues   Problem
 4904    (May 2002)(empii)
         Cell C9 has name defined "test1"
         Cells c10,c11 contain 1(numeric value)
         cell d9 contains formula =SUM(test1:c11)

         Cell d9 results "#NAME?"

         Neither the SUM nor the COUNT functions respect references 
         to named cells.

 5052    (Jul 2002)(empii)
         Excel allows using named ranges in formulas. When importing Excel 
         sheets that use this feature formulas get screwed up.

11799    (Feb 2003)(jwlemke)
         The following cell contents works fine with Excel, but gives an
         error 508 with OO.
         The only way I've found to make this work with OO is:

20587    (Oct 2003)(timdeaton)
         Column F contains the following formula:
            (row 8): =IF(E8=0,"",F$6+SUM(E$7:OFFSET(F8,0,-1)))
         When I open this spreadsheet in (version 1.1.0)
         the formula is corrupted to read as follows:
            (row 8): =IF(E8=0;"";F$6+SUM(#NAME!(E$7;OFFSET(F8;0;-1))))

26004    (Mar 2004)(gardenal)
         while Excel allows you to use dynamic ranges in SUM(), Calc does not.

32483    (Aug 2004)(dankegel)
         =sum(a1:a2:a3) and =sum(d23,d7) display as #NAME
         displayed fine in Microsoft Office and in Gnumeric

37351    (Nov 2004)(micrond)
         when importing an Excel spreadsheet containing formula
         in OpenOffice version 680m59 and 1.1, the formula is not imported
         properly. The problem seems to be in the enumeration C9:E9:I9:...
         that is shown as "#name?(#name?(#name?(..."

38413    (Dec 2004)(urlgrrrl)
         The following formula imported from Excel returns Err:508:
         The problem appears to be with specifying the cell ranges using  
         INDIRECT.  Rewriting the formula as follows does work:
         This problem was reproducted in OOo 2.0 (1.9.62) of 12 Nov.

42678    (Feb 2005)(patrick_catel)
         Preliminary note: I am french and use french OOo 1.1.4 version.
         Involved function names are SOMME and DECALER in french, 
         may be SUM and SHIFT in english.

         Problem is
         SOMME and DECALER functions work separetaly. But to write in a cell
         of an OOo calc-sheet  =SOMME(A3:DECALER(A8;-1;0)) appears impossible
         [the cell references are example] , whatever the way of doing it 
         (import from Excel sheet or direct typing).

43804    (Mar 2005)(tulitanssi)
         The sumif function gives err:508 when args to it are complex, 
         e.g. indirect function etc.

46210    (Apr 2005)(lucianomw)
         The offset function does not return a reference when used as
         parameter to another function. This produces an error message in the
         cell that was calling the function.

47976    (Apr 2005)(nbwepaul)
         Importing a XLS file into OOo Calc it fails to correctly import some
         things. It shows Err:508 (Error: in bracketing) instead. It seems to
         happen when a range is used that contains a formula rather than a
         fixed cell address. This real-life example works in Excel but becomes
         Err:508 when imported into Calc:

         =SUM($'Dept Sales'.$B119:OFFSET($'Dept Sales'.$B119;0;+($B$3-1);1;1))

48761    (May 2005)(sinewalker)
         When entering a cell range into calc for the SUM() function,  it does
         not parse the colon syntax if I use a reference function such as
         INDIRECT(). Non-range  formulas do work however.

         results in Err508 (missing parameter).  

51186    (Jul 2005)(tommyads)
         in any cell zzz within column I below row 2.
         This should always give the sum of all cells above the cell zzz,
         starting from the second row (my first row contains a Header).
         But this does not work!!!

53262    (Sep 2005)(opp)
         If you use INDIRECT as single arguments it works proper.
         If you use INDIRECT within a range-arguments ERR 508 is produced.

53263    (Sep 2005)(opp)
         If you use INDEX as single arguments it works proper.
         If you use INDEX within a range-arguments ERR 508 is produced.

55662    (Oct 2005)(fuechsel)
         DSUM function returns value 'Err:508' under the following conditions:
               =-DSUM(DBASE;"Amount";A19:A20  located on an adjacent worksheet
         where:    A19 has value "Cd" on the worksheet with the formula
                   A20 has value "Hi" on the worksheet with the formula
                   DBASE  is a named variable with value START:END
                   START  is a named variable with value $Acounts:$A$1
                   END    is a named variable with value $Acounts:$J$649
                   Accounts is name of a worksheet in the workbook
                   Amount is a column heading in 'Accounts'
                   Cd is a column heading in 'Acounts'
                   Hi is one of several values appearing in column 'Cd'

55758    (Oct 2005)(patrick_catel)
         test made with OOo 2.0 rc2
         When I open with OOo an Excel sheet containing 
         I get
         Err :508

56051    (Oct 2005)(cerulli)
         Using INDIRECT()location as one of the boundary of a range in SUM,
         gives a (misleading) Err:508.
         Example =SUM(INDIRECT(I18):E17) where I18 = E14, that should give
         the sum in the range (E14:E17), gives an Err:508.

56936    (Oct 2005)(timdeaton)
         When I use the Offset() function inside a SUM() function, it results
         in an error message.  Used in an IF() function, it seems to work
         okay.  But when I loaded an Excel97 sheet with the formula
            =-SUM(H$6:OFFSET(H147;-1;0)) in E147
         Yesterday it gave me an "Error 508" message in that and all dependent
         cells. Today, the same formula in the same place gave me an "#N/A" 
         error message.

57341    (Nov 2005)(andypopely)
         example cell B16 is assigned a name of Q1Medical and cell B19 is 
         assigned a name Q1RealEstate.  When used in the formula 
         =SUM(Q1Medical:Q1RealEstate) to add cells B16 thru B19 
         Calc returns a #NAME? error.

Comment 45 frank 2006-12-01 13:51:26 UTC
*** Issue 72174 has been marked as a duplicate of this issue. ***
Comment 46 dhdawe 2006-12-11 13:02:18 UTC
I was evaluating whether I could switch to Calc.  I have one
particular (and important) spreadsheet that combines pairs of offset results
into ranges.  After much time trying to identify the problem, then trying to
work around it, then trying to find out if others had seen it before, I finally
came across this bug.  Just in case, I even tried the Novell distribution
(version 2.0.2) to see if it had been resolved there.  No luck.

By the way, I cannot bring myself to call this bug a feature ... ":"-ranges
work, and OFFSET() works, but they do not work together.  But most importantly,
this bug is a show-stopper for me.

For what it's worth, thank you timdeaton for your effort.  It seems that you
have put much more effort into this issue than anyone else has.
Comment 47 frank 2007-08-28 12:17:06 UTC
*** Issue 81077 has been marked as a duplicate of this issue. ***
Comment 48 ooo 2007-11-14 13:55:18 UTC
ODFF relevant.
Comment 49 ooo 2007-11-29 19:18:37 UTC
In cws odff:

sc/inc/refdata.hxx  1.5.526.1
sc/source/core/tool/refdata.cxx  1.5.318.1
Comment 50 ooo 2007-11-29 19:21:15 UTC
Created attachment 50005 [details]
test cases
Comment 51 ooo 2007-12-05 11:57:09 UTC
Additional changes in cws odff:

sc/source/core/tool/refdata.cxx  1.5.318.2
Comment 52 ooo 2007-12-05 12:03:55 UTC
Created attachment 50114 [details]
more test cases involving two sheets
Comment 53 ooo 2007-12-05 22:14:53 UTC
More changes:

sc/source/core/tool/refdata.cxx  1.5.318.3
Comment 54 ooo 2007-12-05 22:20:24 UTC
Created attachment 50137 [details]
corrected test cases; in the previous attachment not all were semantically correct
Comment 55 ooo 2008-02-19 11:27:54 UTC
Reassigning to QA for verification.
Comment 56 oc 2008-02-26 12:33:59 UTC
Created attachment 51723 [details]
Comment 57 oc 2008-02-26 12:34:59 UTC
Created attachment 51724 [details]
Testdocuments for Test Case Specification
Comment 58 oc 2008-02-26 12:37:10 UTC
verified in internal build cws_odff
Comment 59 oc 2008-10-17 13:37:30 UTC
closed because fix available in builds OOO300_m9 and DEV300_m33
Comment 60 sinewalker 2008-10-19 22:49:13 UTC
I just wanted to say thanks very much to timdeaton and er for getting this bug
to the stage it's at. I'm looking forward to trying out the OOO300_m9 and
DEV300_m33 builds! There's been a lot of negative energy on this issue and you
deserve some positive thanks from the community.


Comment 61 sinewalker 2008-10-19 23:11:17 UTC
This is great guys, and it made release 3.0 too. Thank you very much.
Comment 62 ooo 2009-07-12 01:59:06 UTC
*** Issue 94406 has been marked as a duplicate of this issue. ***