see help> https://help.libreoffice.org/Calc/Information_Functions#CELL InfoType ADDRESS: =CELL("ADDRESS";Sheet3.D2) returns $Sheet3.$D$2 actually function CELL returns only $D$2 (part $Sheet3. missing) steps to reproduce open new sheet write function =CELL("ADDRESS";Sheet1.D2) actual results: $D$2 expected results: $Sheet1.$D$2
Hi @raal, because you are in the same sheet of the address, so no matter if you use the sheet name or not. =CELL("ADDRESS";D2) =CELL("ADDRESS";Sheet1.D2) give the same result if the formula is in Sheet1. I think not a bug.
Hi Miguel, =CELL("address",Sheet2.A1) works -> $Sheet2.$A$1 but in ODF specification is not mentioned it should work only for other sheet: ADDRESS - Returns the absolute address of the cell. *The sheet name is included if given in the reference.* http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018348_715980110
Right. But as this is a behavior inherited from OOo, change how it works could break how it is used without notice, I think usually as part of an INDIRECT() or a DDE() function. Do you know how excel does?, gnumeric do the same, and 123 always returns only the address without sheet, but it has an option to get the sheetname not available here.
(In reply to m.a.riosv from comment #3) > > Do you know how excel does?, gnumeric do the same, and 123 always returns > only the address without sheet, but it has an option to get the sheetname > not available here. in excel: =CELL("address";Sheet1!A1) -> $A$1 (same sheet =CELL("address";Sheet2!A2) -> [Book1]Sheet2!$A$2
So or broke compatibility or ask for change ODF definition adopting what is in the street :).
We behave the same as Excel and Gnumeric and actually ODF OpenFormula should be adapted to reality, "The sheet name is included if given in the reference" should be amended with "and does not reference the same sheet as the sheet the expression is evaluated on" or some such. I'll write an OASIS-TC comment. Nevertheless, this is a documentation bug => component Documentation
Just to add more details to the mess: Actually for a different sheet the return string depends on the current reference style, which doesn't make things easier. For LibreOffice, in CalcA1 it is "$Sheet1.$A$1", in ExcelA1 it is "Sheet1!$A$1" and in ExcelR1C1 it is "Sheet1!R1C1", but that is implementation specific and should be congruent with what ADDRESS() delivers and INDIRECT() can digest. LibreOffice supports all three syntax modes. Excel returns "[Book1]Sheet2!$A$1" for a new unsaved document or "[Book2.xlsx]Sheet2!$A$1" for a saved document, so includes even the file name (which must be a legacy of when each sheet was a separate file and documents with more than one sheet didn't exist). In R1C1 reference style Excel returns "[Book1]Sheet2!R1C1" or "[Book2.xlsx]Sheet2!R1C1". Gnumeric version 1.12.29 apparently never returns a sheet name.
For quick access, the corresponding documentation page is currently this one: https://help.libreoffice.org/latest/en-GB/text/scalc/01/04060104.html
Seeing the history I think better close as not a bug.