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.
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.