Bug 44419 - Allow entire column or entire row ranges in formulae, e.g. A:A or 1:1
Summary: Allow entire column or entire row ranges in formulae, e.g. A:A or 1:1
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: lowest enhancement
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: target:5.1.0 target:5.0.0.2 target:4...
Keywords:
: 48571 69843 85417 92451 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-01-03 04:24 UTC by Ed
Modified: 2016-10-25 19:21 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel spreadsheet which is corrupted by LibreOffice (8.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-06-22 22:56 UTC, Jerzy Tyszkiewicz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ed 2012-01-03 04:24:48 UTC
LibreOffice does not appear to allow column or row ranges in formulae.  Examples of these would be sum(A:A) to add all the values in column A, sum(A:C) to add all the values in columns A, B and C, sum(1:2) to add all the values in rows 1 and 2, etc.  

MS Excel has this feature.  

Personally I think it would be incredibly useful, and it counts as a deficiency against LibO that it does not offer this.  

One reason in support of this is that it is much simpler and quicker to enter such ranges into formulae, rather than having to check and/or select ranges of actual cells.  

Another reason is that this feature is very powerful: if values are added anywhere within the specified column range or row range, they would cause the formula to be recalculated.  For example, if we want to total all the values in column A, which at one point contains values in its first 10 rows, we currently have to use a formula of sum(A1:A10).  If we then insert a new value into cell A11, that formula will ignore this, whereas a formula of sum(A:A) would be recalculated.  I acknowledge we could get round this by using a formula of, say, sum(A1:A20), and this would allow us to insert 10 extra values into column A, but of course we still run into problems when we find ourselves inserting yet another value, into cell A21 - Sod's law.  Again, we could use a formula of sum(A1:A1048576), but this is very cumbersome and it is obscure: it hides our true intent.  

A corollary to this enhancement would be to adapt the ADDRESS function to allow it to return not just a cell reference, but a row reference or a column reference.
Comment 1 Ed 2012-01-03 04:50:36 UTC
I have just come across the Calc feature where textual column and row labels are recognised automatically as names for column and row ranges respectively.  Very nice feature, and this goes some way towards meeting my enhancement request: it does appear to behave as desired for single-row and single-column ranges.  

However, as far as I can see it does not allow you to handle ranges of multiple columns or rows.  For example, sum('Col 1':'Col 3') does not work.  Although cases where we want to add together the values in multiples columns or rows might be regarded as rare, handling references to multiple columns or rows is vital to the VLOOKUP and HLOOKUP functions.  

One way of meeting this enhancement request might be to enhance this feature, so that something like sum('Col 1':'Col 3') would work.  This could turn out to be a greater complication, and I leave that decision to the designers!
Comment 2 Markus Mohrhard 2012-01-08 19:19:05 UTC
See OpenFormula 5.8. This is a valid reference according to OpenFormula/ODF 1.2:

B:B, 3:15, ...
Comment 3 Joel Madero 2012-07-03 10:53:08 UTC
I'm unable to use SUM(A:A) either, I'm getting a #name error when I try it. If this is supposed to be supported (according to last comment Open Formula should support A:A) then this is a bug, not an enhancement request. 

The second part may very well be an enhancement (ie. multiple columns such as SUM(A:B:D:F). 

Because part is a bug, part is not, I'm going to leave as enhancement but set as HIGH priority. Thanks for helping us make LO better for everyone.
Comment 4 bugquestcontri 2013-03-06 02:54:50 UTC
I was about filing a bug report when I found this one. 
It would be great if this bug fixed / enhancement implemented.

BTW, the topic popped also up again in AskLibO. Thus it seems there is some kind of urgency.

I am always grateful to our devs for their excellent work.
Comment 5 lee.eden 2013-10-09 20:38:03 UTC
This seems to have been fixed in version 4.0.4.2 which is great! It works for me when I have set the formula settings to "Excel A1" (tools>options>formula>calc>formula>formula syntax).

Final wish is that when writing a formula, clicking with the mouse on the column/row header would automatically write the column/row reference as it does with single cells or ranges. To explain what I am after, I would like to be able to type as follows:
  "=VLOOKUP("
  then click on cell A2 [formula bar now shows "=VLOOKUP(A2"] - this bit works
  then type comma [formula bar now shows "=VLOOKUP(A2,"]
  then click on the "G" for the header of column G and drag the mouse across to column S and have the formula bar showing "=VLOOKUP(A2,G:S" - this bit doesn't work (also in Excel it shows that 13 columns have been selected)
  then type ",13,false)" to complete the formula as "=VLOOKUP(A2,G:S,13,false)"

This is about productivity using Calc, and becomes especially key where referencing row/column ranges in an other sheet (where manually typing formulas really slows you down). I would say "best practice" for workbook structure is to have a "summary" sheet which VLOOKUPs or SUMIFs to a "detail" sheet, so this is a really common task that I do many times a day in my work, so remains a productivity barrier to my final full-time switch from Excel.
Comment 6 kaesezeh 2013-11-09 16:18:10 UTC
*** Bug 69843 has been marked as a duplicate of this bug. ***
Comment 7 Сергій Загорія 2014-07-22 19:29:11 UTC
FYI: My father just refused to use Calc because it's extremely uncomfortable to select a column from another file. And I agree with him that it's extremely unobvoius in the current state. Is clicking on the title patented or somehow difficult to implement?
Comment 8 raal 2014-10-25 06:28:21 UTC
*** Bug 85417 has been marked as a duplicate of this bug. ***
Comment 9 Alex Puntigam 2014-12-21 19:24:32 UTC
I have just tested this out with the Excel A1 setting, and it works, which is great.

However, I think it would still be extremely convenient if this worked under the default Calc A1 setting also.  I use the A:A type column reference daily in all my spreadsheets, I find it incredibly useful to ensure people don't break the spreadsheets I build in the business environment (where people are incapable of NOT breaking stuff).
Comment 10 aaker 2015-01-03 21:15:53 UTC
Unfortunately setting "formula syntax" to "Excel A1" is not really satisfying because internally column syntax "=SUM(A:A)" is referenced as "=SUM(A$1:A$1048576)". Inserting row(s) will lead to "#REF!" Value.

This seems to be a basic bug.
Comment 11 m.a.riosv 2015-04-12 22:54:19 UTC
*** Bug 90573 has been marked as a duplicate of this bug. ***
Comment 12 lee.eden 2015-04-13 10:16:05 UTC
This bug has had status "new" since January 2012. 
We can consider this to be a bug (not enchancement) as the whole row/whole column reference should be supported according to the specification (paragraph 4.8 of the Open Document Format for Office Applications (OpenDocument) Version 1.2 (
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CB4QFjAA&url=https%3A%2F%2Fwww.oasis-open.org%2Fcommittees%2Fdownload.php%2F37765%2FOpenDocument-v1.2-part2-cd02-rev05-editor-revision.odt&ei=kZQrVeqPEZfdava-gIAK&usg=AFQjCNFep36B2Tlk0Vdz9tuCz8sp8QLd1Q&sig2=p4OnoQUkrwpI3AsoE9ePVg&bvm=bv.90491159,d.d2s )

"The syntax supports whole-row and whole-column references"

So what do we need to do in order to:
- change the status of this from NEW to ASSIGNED
- change the importance from "enchancement" to "bug"
?

Although there is some progress in recognition of "A:A" [.A] in formulas, it is still broken as insertion of a row in the range referenced by "A:A" [.A] results in a #REF! error because A:A is being internally stored as "A$1:A$1048576" [.A$1:$A1048576]. This effectively makes it impossible to maintain files containing these whole-row/whole-column references . Also the lack of functionality to be abile to click column headings when writing formulas and have the A:A [.A] inserted into the formula is a significant productivity barrier.
Comment 13 shunesburg69 2015-05-14 00:19:23 UTC
It would be good to fix it before the release of LO 5.0,
we can't work properly with this bug.
Comment 14 Eike Rathke 2015-06-16 16:25:13 UTC
*** Bug 48571 has been marked as a duplicate of this bug. ***
Comment 15 Jerzy Tyszkiewicz 2015-06-22 22:56:00 UTC
Created attachment 116744 [details]
Excel spreadsheet which is corrupted by LibreOffice
Comment 16 Jerzy Tyszkiewicz 2015-06-22 22:59:24 UTC
Worse still, LibreOffice corrupts spreadsheets created under Excel, which contain references to whole rows in the form '1:1' (I use LibreOffie 4.3.7.2 with Excel A1 syntax).

E.g., Excel's meaningful =INDEX(1:1,INDEX(2:2,3))
becomes LibreOffice's =INDEX(1:1INDEX(2:2)), which makes no sense and produces an error.

The most perverse form is that Excel's =INDEX(2:2,2)
is turned into LibreOffice's =INDEX(2:2), which is syntactically correct, but produces generrally a different value.
Comment 17 Commit Notification 2015-06-25 18:22:05 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c48af2b55a60492565b987ee65d98edc6bd94533

a singleton must be fully parsed to be valid, tdf#44419 related

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 18 Commit Notification 2015-06-25 18:27:55 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=69e5b158e3d5cd37b405315bd995788519b4a17b&h=libreoffice-5-0

a singleton must be fully parsed to be valid, tdf#44419 related

It will be available in 5.0.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 19 Commit Notification 2015-06-25 19:31:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7ea962bd8f2e5303b7719add1f47bb79047b0739&h=libreoffice-4-4

a singleton must be fully parsed to be valid, tdf#44419 related

It will be available in 4.4.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 20 Eike Rathke 2015-06-25 19:36:56 UTC
More to come.. taking.
Comment 21 Commit Notification 2015-06-25 20:58:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=71dba79c1c84d693a59c53a8965caef0ddd1c2cc

tdf#44419 in second reference part stop number parsing at separator

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 22 Commit Notification 2015-06-25 21:07:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=df06ddf79cf0b5aca037630504a07dd8b2341a43&h=libreoffice-5-0

tdf#44419 in second reference part stop number parsing at separator

It will be available in 5.0.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 23 Eike Rathke 2015-06-25 21:14:34 UTC
Problems of comment 16 and attachment 116744 [details] solved, one more review for 4-4 pending at https://gerrit.libreoffice.org/16496
Comment 24 Commit Notification 2015-06-26 13:20:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0c8778ce1df92ca3bc2a8dd2f64568fb257e9e39

tdf#44419 allow A:A and 1:1 references also in Calc A1 and ODF syntax

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 25 Commit Notification 2015-06-26 13:50:16 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7f939c3e6edb86f0b7e66e06dff8743830c18ef9&h=libreoffice-5-0

tdf#44419 allow A:A and 1:1 references also in Calc A1 and ODF syntax

It will be available in 5.0.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 26 Eike Rathke 2015-06-26 14:03:01 UTC
Pending review for 4-4 at https://gerrit.libreoffice.org/16518
Comment 27 Commit Notification 2015-06-26 14:37:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4091b4fbdce40262eba46ab94653287b1fd928e8

entire rows/cols have absolute col/row anchors, tdf#44419 follow-up

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 28 Commit Notification 2015-06-26 14:41:40 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=627d50e7d432463d6a625ba5b387df7e61e4fc76&h=libreoffice-5-0

entire rows/cols have absolute col/row anchors, tdf#44419 follow-up

It will be available in 5.0.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 29 Commit Notification 2015-06-26 15:54:03 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=eeea41e2e726dd5d69cd323909e1666efb9de472

tdf#44419 display entire column/row references as A:A/1:1

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 30 Commit Notification 2015-06-26 16:22:57 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7511c2af96f9b0a6830c64d6a80cb4d6c8e7af8f&h=libreoffice-5-0

tdf#44419 display entire column/row references as A:A/1:1

It will be available in 5.0.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 31 Commit Notification 2015-06-29 11:12:48 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5dd29024a5eb98393eba1c19559ea6c4ed65ac6c

unit test for tdf#44419

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 32 Commit Notification 2015-06-29 11:52:15 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=621a7689375a4c23bb1dd23c2643c9ac42aa29a9

insert/shift unit test for tdf#44419

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 34 Commit Notification 2015-06-30 11:43:15 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=fb799a2f6fc21bde34cd36763f6003b845c6a093&h=libreoffice-4-4

tdf#44419 in second reference part stop number parsing at separator

It will be available in 4.4.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 35 Adolfo Jayme 2015-06-30 12:28:39 UTC
Blocker? WTF?
Comment 36 Jerzy Tyszkiewicz 2015-07-01 10:36:12 UTC
(In reply to Adolfo Jayme from comment #35)
> Blocker? WTF?

As far as I remember I set this value. Here is my explanation:

Have a look at the property described in my comment #16: LibreOffice was able to silently replace a valid formula present in an .xslx spreadsheet by another one, also valid, but of different semantics and therefore producing a different value. 

For an end-user like me, such a discovery is an absolute imperative to stop using the affected spreadsheet software immediately. Imagine that you get a spreadsheet from you tax advisory. Would you be brave enough to open it in LibreOffice, print out and send to the tax administration, if you knew that the tool could alter the formulas and (consequently) the numbers in the tax document? Would you be brave enough to make other serious decisions concerning your business, family finance, etc., under such circumstances?

From the end-user's point of view a software which crashes every second run is actually much safer to use than one, which once in 1000 cases suddenly changes the data it presents to the user without any notice.
Comment 37 Óvári 2015-07-01 11:36:12 UTC
Eike Rathke work in enabling correct references in LibreOffice is to be congratulated.

As Jerzy Tyszkiewicz has raised a concern about LibreOffice Calc formulae capabilities, it might be advisable to continue to use Excel until the resolution of a circular reference bug:
https://bugs.documentfoundation.org/show_bug.cgi?id=92468

LibreOffice Calc formula does not correctly flag a circular reference and provides in the spreadsheet while Excel does flag the circular reference correctly.

Listed at https://bugs.documentfoundation.org/show_bug.cgi?id=92468 is solution to ensure that any formulae entered or pasted, LibreOffice Calc should check and change if necessary each range in the LowerBound:UpperBound to be in ascending order for both column and row. For example:
B10:B1 should be changed and stored as B1:B10
C:A      --> A:C
A3:B2    --> A2:B3
B2:A3    --> A2:B3
$E3:C$8  --> C3:$E$8

The Name Box in LibreOffice Calc automatically list the range correct if you select cells from E8 to C3, or E3 to C8, or C3 to E8 or C8 to E3. Perhaps the code in the Name Box could be utilised in making the formulae in ascending order.

I'm not sure if:
1. this description needs to be copied into bug 92468; and
2. the status of this ticket needs to be changed.

If someone agrees with the UNCONFIRMED bug reports listed with this ticket, it would be appreciated if you would confirm them.

Thank you
Comment 38 Eike Rathke 2015-07-06 22:21:57 UTC
@Ovari:
Would you please stop advising the use of a different product? Thank you.

Problems in a feature implementation get their own bugs, so opening a new bug was correct.
Comment 39 Buovjaga 2015-10-05 09:51:23 UTC
*** Bug 92451 has been marked as a duplicate of this bug. ***