Issue 82189 - Add support for A:A and 1:1 in formulas as whole column and whole row references.
Summary: Add support for A:A and 1:1 in formulas as whole column and whole row referen...
Status: CLOSED DUPLICATE of issue 20495
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.3
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Depends on:
Reported: 2007-10-02 17:36 UTC by crompviv
Modified: 2007-11-22 15:51 UTC (History)
1 user (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description crompviv 2007-10-02 17:36:47 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 create a formula: =sumif(A1:A65535;"=SUM";B1:B65535); 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
Comment 1 crompviv 2007-10-02 17:40:47 UTC
The example sumif formulas should have read:
Comment 2 frank 2007-11-22 15:50:42 UTC
double to Issue 20495

*** This issue has been marked as a duplicate of 20495 ***
Comment 3 frank 2007-11-22 15:51:14 UTC
closed double