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.
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!
See OpenFormula 5.8. This is a valid reference according to OpenFormula/ODF 1.2: B:B, 3:15, ...
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.
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.
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.
*** Bug 69843 has been marked as a duplicate of this bug. ***
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?
*** Bug 85417 has been marked as a duplicate of this bug. ***
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).
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.
*** Bug 90573 has been marked as a duplicate of this bug. ***
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.
It would be good to fix it before the release of LO 5.0, we can't work properly with this bug.
*** Bug 48571 has been marked as a duplicate of this bug. ***
Created attachment 116744 [details] Excel spreadsheet which is corrupted by LibreOffice
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.
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.
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.
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.
More to come.. taking.
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.
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.
Problems of comment 16 and attachment 116744 [details] solved, one more review for 4-4 pending at https://gerrit.libreoffice.org/16496
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.
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.
Pending review for 4-4 at https://gerrit.libreoffice.org/16518
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.
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.
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.
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.
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.
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.
Please see: https://bugs.documentfoundation.org/show_bug.cgi?id=92426 https://bugs.documentfoundation.org/show_bug.cgi?id=92427 Thank you
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.
Blocker? WTF?
(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.
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
@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.
*** Bug 92451 has been marked as a duplicate of this bug. ***