Description: Hello. Without exaggeration it was shocking to me to find that this feature is not in Calcs. But because this feature seems to me essential i don't think it was missed but rather there is reason not to support it and im curious about the reason. So in Google Sheets, the product i started my spreadsheet experience with, once i wanted to include in my array all cells from columns A, B, C and all rows beginning from 4 and down. Intuitively i tried "A4:C", and it worked. I did not read about it anywhere, it just makes sense. Then i was, mildly put, surprised to find this does not work in LibreOffice Calcs. I was like, there is no way. Out of curiosity i tried this in other products, WPS Office, OnlyOffice, even MS Office. It didn't work. Ok, at this point there has to be a reason, can someone please explain why? btw i tried this a while ago so maybe it is now supported by some of the products. Thank you. Steps to Reproduce: 1.ranges like "a3:a", "a1:4" Actual Results: not working Expected Results: should be supported Reproducible: Always User Profile Reset: No Additional Info: No other information
And what that ranges it's expected should be?
"a3:a" - cells of column "A" beginning from row "3" and below. "a1:4" - all cells of rows from "1" to "4"
[Automated Action] NeedInfo-To-Unconfirmed
Sorry, but doing a test, it is not how Google sheets saves as ods/xlsx file. "a3:a" -> =SUM(A3:A1000) "a1:4" -> =SUM(A1:Z4)
I couldn't find a similar enhancement request. I can see the appeal of this convenience feature, given that we already can use references of the type A:C and 3:5, so why not mix them, but I'd like to hear from the XU/Design team and Eike. Has this been requested before? I think such a range definition is pretty unambiguous.
My first thought was that this is not defined in ODF but it is ---- 9.2.4 Column and Row Range Addresses Column and row addresses are cell range addresses that reference entire rows or columns. The syntax of a row address is the same as a cell address, except the alphabetic values that indicate the column are omitted. The syntax of a column address is the same as a cell address, except the numeric values that indicate the row are omitted. ---- https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1415618_253892949 GSheet silently exports the formula "=sum(A1:3)" as "table:formula="of:=SUM([.A1:.Z3])" which is of course completely wrong (I guess the same is true for docx). Bottom line: be cautious with this type of range definition.
(In reply to Heiko Tietze from comment #6) > ...which is of course completely wrong Not completely wrong since GSheet has only A:Z columns, and after adding one the exported formula is A1:AA1.
(In reply to Heiko Tietze from comment #6) > My first thought was that this is not defined in ODF but it is No, it is not. > 9.2.4 Column and Row Range Addresses > Column and row addresses are cell range addresses that reference entire rows > or columns. > > The syntax of a row address is the same as a cell address, except the > alphabetic values that indicate the column are omitted. > > The syntax of a column address is the same as a cell address, except the > numeric values that indicate the row are omitted. Note that definition is in the global schema definition. That definition to me is ambiguous and quite certainly did not want to say that one could mix the notations of cell range addresses and entire column or row range addresses. It likely is wrong worded and instead of "a row address is the same as a cell address" should read "a row address is the same as a cell range address" and then "except the alphabetic values that indicate the column are omitted" actually would make sense. Similar for column address. The ODFF formula specification is very explicit about that, see 5.8 References https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#References The syntax says you can have either SheetLocatorOrEmpty '.' Column ':' '.' Column or SheetLocatorOrEmpty '.' Row ':' '.' Row but no mix of SheetLocatorOrEmpty '.' Column Row ':' '.' Column or any such construct. So if at all this would be an UI only feature, which when entered resolves to the last row or column for the missing part. Note that an input of A:A3 wouldn't make sense and should resolve as A3:A and thus A3:A1048576, if not error.
(In reply to Eike Rathke from comment #8) > Note that definition is in the global schema definition. That definition to > me is ambiguous and quite certainly did not want to say that one could mix > the notations of cell range addresses and entire column or row range > addresses. It likely is wrong worded and instead of > "a row address is the same as a cell address" > should read > "a row address is the same as a cell range address" > and then "except the alphabetic values that indicate the column are omitted" > actually would make sense. Similar for column address. @Regina, I guess that if the current wording needs a revision, then it would be wise to propose an improvement (be)for(e) the future ODF 1.4.
*** Bug 159397 has been marked as a duplicate of this bug. ***