Summary: | Support mixed column/row references like in Google Sheets like =SUM(A1:3) | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | aydsys |
Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | UNCONFIRMED --- | ||
Severity: | enhancement | CC: | erack, heiko.tietze, miguelangelrv, p_kongstad, rb.henschel, stephane.guillou |
Priority: | medium | Keywords: | needsDevAdvice |
Version: | Inherited From OOo | ||
Hardware: | All | ||
OS: | All | ||
Whiteboard: | |||
Crash report or crash signature: | Regression By: |
Description
aydsys
2023-09-04 15:06:04 UTC
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. *** |