Apache OpenOffice (AOO) Bugzilla – Issue 20495
Q-PCD EaseOfUse-NN-02 Allow (A:A) as range in functions
Last modified: 2024-02-05 10:47:56 UTC
Task tracking system for Childs PLEASE CREATE CHILD-TASKS IN ISSUEZILLA! Source QA/Customer Category Calc Product Requirement Allow (A:A) as range in functions Customer Need/Problem A compatible range for a whole column is needed (e.g. =sum(A:A) ) Comment - Eng Effort - Eng Owner Niklas Nebel Product Concept In formulas in other applications, whole columns or rows can be referenced using just the column or row names, like 'A:B' or '1:2'. In the Excel file import these references are already converted to 'A1:B32000' or 'A1:IV2', so nothing is lost. But a user who tries to enter such formulas in SO/OOo will get an error. We dont want to change our definition of cell references, because it is part of the documented file format. Instead, we will do the same conversion as in the Excel import during formula input. The 'INDIRECT' function also has to be extended. Functional Specification -
added keyword Q-PCD
*** Issue 23098 has been marked as a duplicate of this issue. ***
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.
*** Issue 50303 has been marked as a duplicate of this issue. ***
changed summary
*** Issue 68207 has been marked as a duplicate of this issue. ***
The suggestion to convert automatically any entry like (A:A) to (A1:A65563) is _not_ a sufficient solution! If you mean to sum the entire culumn A by using =SUM(A1:A65563) in cell B1, and extend it to a row below by dragging, the second row cell (B2) will take a value of "=SUM(A2:A#REF!)", obviously not the desired result (to keep selecting the entire column). In addition there is a compatibility problem between old sheets that contain 32000 rows and new sheets that contain 65563 rows. (A:A) and (A1:A65563) are just not the same, but a very different qualitiy of information. Up to now, OOo lacks the possibility to express a reference to an entire column or an entire row.
Please consider to enable entering a reference to an entire row/column by a mouse click on the row/column header.
*** Issue 82189 has been marked as a duplicate of this issue. ***
Note that the Excel 2007 file import does not convert these references automatically which effectively breaks the compatibility for those sheets using this syntax.
Set target and cc myself.
*** Issue 112049 has been marked as a duplicate of this issue. ***
*** Issue 112254 has been marked as a duplicate of this issue. ***
Logical extensions or parts of this functionality, depending on whether you want these as separate issue(s) or not, would the the ability to click the SUM button (sigma), and then the column or row title, and get the whole column or row; or several columns or rows. To be able to think in columns or rows, no matter how many are supported in future versions of the software, would also include being able to use the Format Paintbrush to paint a format on a column or row. Painting a format on the whole sheet by clicking on the heading square in the upper-left corner would complete this user interface suite of niceties. Note that one can already set formats this way through Cell | Format; just, not with the Format Paintbrush. Think of this as one of the pre-Apache things the developers never got around to; but now, it is now a matter of just catching up to where Excel was 10-15 years ago.
*** Issue 105444 has been marked as a duplicate of this issue. ***
This is now a defect since import of a proper .ods having A:A produced by Excel will cause a failure on entry into AOO as of 4.1.2. There is a patch under review and there is also some developer discussion on how to accomplish this with appropriate attention to interoperability with earlier ODF 1.2 (and OpenForumula) supporting versions of OpenOffice.org, AOO, and also LibreOffice before this was repaired there.
Reset the assignee to the default "issues@openoffice.apache.org".
*** Issue 126915 has been marked as a duplicate of this issue. ***
(In reply to orcmid from comment #16) > There is a patch under review Where is this patch? I bump this defect because incompatibility increase now
(In reply to oooforum (fr) from comment #19) > (In reply to orcmid from comment #16) > > There is a patch under review > Where is this patch? It was committed and then reverted because the author copied it from LibreOffice.
It's a shame, but I don't really see how we can invent another code syntax to fix the issue. :-/