Apache OpenOffice (AOO) Bugzilla – Issue 4904
ODFF: Dynamic ranges of named cells, OpCode ocRange
Last modified: 2013-08-07 15:15:24 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 fine.
Confirmed. 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, =SUM(test1:a8) or =COUNT(test1:a8) will always result in a #NAME? error. -Josh Berkus
Names as parameters in areas are not supported by OOo => Enhancement
*** Issue 5052 has been marked as a duplicate of this issue. ***
*** Issue 11799 has been marked as a duplicate of this issue. ***
Support names (of cells) as parameters in areas. Note: This is also an Excel related issue
started
*** Issue 20587 has been marked as a duplicate of this issue. ***
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
reassigning
reassigning I said..
Accepted.
*** Issue 26004 has been marked as a duplicate of this issue. ***
Add blocking dependency to issue 20494, raise priority to P3.
according to the announcement on releases (http://www.openoffice.org/servlets/ReadMsg?list=releases&msgNo=7503) this issue will be re-targeted to OOo Later.
Sigh.. seems the list of issues to be retargeted was created before I changed priority of this one.. so back to OOo2.0.
Sigh again.. product management decided to postpone remaining features from OOo2.0 to OOoLater.
*** Issue 32483 has been marked as a duplicate of this issue. ***
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 :-)
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.
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. Frank
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.
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.
Dan, 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. Eike
*** Issue 37351 has been marked as a duplicate of this issue. ***
*** Issue 38413 has been marked as a duplicate of this issue. ***
*** Issue 42678 has been marked as a duplicate of this issue. ***
*** Issue 43804 has been marked as a duplicate of this issue. ***
This problem has been around for almost three years, which is a very long time. When will it be fixed?
*** Issue 46210 has been marked as a duplicate of this issue. ***
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?
*** Issue 47976 has been marked as a duplicate of this issue. ***
Dan, 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. Eike
*** Issue 48761 has been marked as a duplicate of this issue. ***
*** Issue 51186 has been marked as a duplicate of this issue. ***
*** Issue 53262 has been marked as a duplicate of this issue. ***
*** Issue 53263 has been marked as a duplicate of this issue. ***
*** Issue 55662 has been marked as a duplicate of this issue. ***
*** Issue 55758 has been marked as a duplicate of this issue. ***
*** Issue 56051 has been marked as a duplicate of this issue. ***
*** Issue 56936 has been marked as a duplicate of this issue. ***
Has there been any progress on this issue? Any idea yet when a resolution is likely to see the light of day?
*** Issue 57341 has been marked as a duplicate of this issue. ***
This item truly shows the weakness of Open Source.
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. =SUM(INDIRECT("C"&F8+1):C8) The only way I've found to make this work with OO is: =SUM(INDIRECT("C"&F8+1&":C8")) 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 OpenOffice.org (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 "=IF(COUNT(C9:E9:G9:I9:K9:M9:O9)=0;"";((C9+E9+G9+I9+K9+M9+O9)/COUNT(C9:E9:G9:I9:K9:M9:O9)))" 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: =CORREL($G60:INDIRECT(CONCATENATE("g"&F6));H60:INDIRECT(CONCATENATE("h"&F6))) The problem appears to be with specifying the cell ranges using INDIRECT. Rewriting the formula as follows does work: =CORREL($G60:G244;H60:H244) 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. SUM(INDIRECT(ADDRESS(CELL("ROW";A8);3)):INDIRECT(ADDRESS(CELL("ROW";A10);3))) results in Err508 (missing parameter). 51186 (Jul 2005)(tommyads) Enter =SUM(I2:ADDRESS(ROW()-1;COLUMN();4)) 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: formula: =-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 =SUM(B1:OFFSET(B7;-1;0))] 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.
*** Issue 72174 has been marked as a duplicate of this issue. ***
I was evaluating whether I could switch to OpenOffice.org 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.
*** Issue 81077 has been marked as a duplicate of this issue. ***
ODFF relevant.
In cws odff: sc/inc/compiler.hxx 1.32.36.7 sc/inc/refdata.hxx 1.5.526.1 sc/inc/token.hxx 1.13.60.1 sc/inc/tokenarray.hxx 1.10.30.1 sc/source/core/data/cell.cxx 1.39.60.2 sc/source/core/inc/interpre.hxx 1.31.38.1 sc/source/core/tool/compiler.cxx 1.70.56.9 sc/source/core/tool/interpr2.cxx 1.33.56.1 sc/source/core/tool/interpr4.cxx 1.51.56.3 sc/source/core/tool/refdata.cxx 1.5.318.1 sc/source/core/tool/token.cxx 1.29.30.1 sc/source/filter/excel/excform.cxx 1.48.60.1 sc/source/filter/excel/excform8.cxx 1.44.60.1 sc/source/filter/inc/excform.hxx 1.19.90.1
Created attachment 50005 [details] test cases
Additional changes in cws odff: sc/inc/compiler.hxx 1.32.36.8 sc/inc/tokenarray.hxx 1.10.30.2 sc/source/core/tool/compiler.cxx 1.70.56.10 sc/source/core/tool/compiler.cxx 1.70.56.11 sc/source/core/tool/refdata.cxx 1.5.318.2 sc/source/core/tool/token.cxx 1.29.30.2 sc/source/core/tool/token.cxx 1.29.30.3
Created attachment 50114 [details] more test cases involving two sheets
More changes: sc/source/core/tool/refdata.cxx 1.5.318.3 sc/source/core/tool/token.cxx 1.29.30.4 sc/source/core/tool/token.cxx 1.29.30.5
Created attachment 50137 [details] corrected test cases; in the previous attachment not all were semantically correct
Reassigning to QA for verification.
Created attachment 51723 [details] TestCaseSpecification
Created attachment 51724 [details] Testdocuments for Test Case Specification
verified in internal build cws_odff
closed because fix available in builds OOO300_m9 and DEV300_m33
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. Cheers, m
This is great guys, and it made release 3.0 too. Thank you very much.
*** Issue 94406 has been marked as a duplicate of this issue. ***