Apache OpenOffice (AOO) Bugzilla – Issue 82189
Add support for A:A and 1:1 in formulas as whole column and whole row references.
Last modified: 2007-11-22 15:51:14 UTC
Description of Problem: It is not possible to create cell ranges that span an entire row or column without using cumbersome notation such as A1:A65535. This is awkward, and also causes formulas to break easily if a cell range has cells removed or inserted in it. Excel supports this syntax, and users that are migrating (as well as old-time users) are used to using it. I give below a simple example of where cell ranges that require start and end numbers are awkward. Steps to reproduce the problem: 1.You wish to create a formula to sum all of the cells in a column that have the value "TAX" in an ajacent column 2.you create a formula: =sumif(A1:A65535;"=SUM";B1:B65535); 3.you delete a cell in column A, shifting all the cells up.(Because say, you had your figures mis-aligned with the TAX entries Actual Results: Now the formula will break, as the range A1:A65535 has become A1:A65534, and the criteria range A1:A65535 is not the same size as the summing range B1:B65535. 5.This is a trivial example - there are other circumstances where the intent is to always reference the whole column (or row), but inserts/deletes cause the ranges to shift so they no longer encompas the entire column. Expected Results: If the ranges had been able to be written as =sumif(A:A;"=SUM";B:B) then the problem would not occur - and the formula would be alot simpler too. This How often does this happen? It is a constant irritation having to write entire column references as $A$1:$A$65536 instead of just A:A to prevent this problem occuring, and even after having used Open Office exclusively now for 4 years I still miss this feature (that Excel supports) greatly. Additional Information (OS patch level, drivers, library version, etc.) WindowsXp/Ubuntu 7.04
Correction: The example sumif formulas should have read: =sumif(A1:A65535;"=TAX";B1:B65535); and =sumif(A:A;"=TAX";B:);
double to Issue 20495 *** This issue has been marked as a duplicate of 20495 ***
closed double