Created attachment 149785 [details] Cell D13 should probably return an integer when referenced The cell E15 is containing a function which is not evaluate properly. This file is working fine, ie the cell function is evaluate correctly, with MS office and imported correctly by Google doc. There are something which is not working as expected with LibreOffice. I am suspecting that the problem is in reality with the cell D13 which is giving an integer on Google doc. Thanks for libreoffice.
What values should be D13 and E15
In E15: 20 (20 jours ouvrés dans le mois) In D13: what is show (a list of month) is correct but the result when interrogating for the cell should be an integer: 43497 And C13 (which seems to be linked to D13): 26 I honestly do not understand the function inside this sheet. It was given to me by a coworker and I was not able to use it with libreoffice so the bug report.
it seems to work if you change the sheetname in the functions from "calendar!D" to LO syntax "calendar.D" D13: =INDIRECT("calendar.D"&(C13+1)) E15: =SUMIF(B18:B48;">0";C18:C48)+(SUMIF(B18:B48;">0";D18:D48))&" ("&INDIRECT("calendar.E"&(C13+1))&" jours ouvrés dans le mois)" according to https://help.libreoffice.org/6.1/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153181 > If you open an Excel spreadsheet that uses indirect addresses calculated from > string functions, the sheet addresses will not be translated automatically. > For example, the Excel address in INDIRECT("filename!sheetname"&B1) is not > converted into the Calc address in INDIRECT("filename.sheetname"&B1). this is not a bug
Thanks a lot. Indeed it solved my problem. I am not completely agree about not a bug. That happens not with an ODF file but an docx import and if I modify the file as you mention to solve the problem on LO, the same problem is happening now (with the .) on Excel. I found it problematic because that gave argument to my colleague that I should not use LO since to share document is problematic. Thanks anyway for the information.
(In reply to Nicolas from comment #4) > Thanks a lot. Indeed it solved my problem. I am not completely agree about > not a bug. That happens not with an ODF file but an docx import and if I > modify the file as you mention to solve the problem on LO, the same problem > is happening now (with the .) on Excel. you could try something like this: =IF(ISERROR(INDIRECT("calendar!D"&(C13+1)));INDIRECT("calendar.D"&(C13+1));INDIRECT("calendar!D"&(C13+1)))
I agree but my point is that it is impossible to ask a MS excel user to do it. They will not understand the reason. I think that the behaviour should be different if the file is xlsx or on ODF but I am thinking in term of interoperability and try to convince people that LO can be used in a professional environment.
There is an option Menu/Tools/Options/LIbreOffice calc/Formula - Detailed Calculation Setting - Custom - Reference syntax for string reference, that I think should solve the issue selecting 'Calc A1 | Excel A1' or 'Excel A1' file:///C:/Program%20Files/LibreOffice/help/en-US/text/shared/optionen/detailedcalculation.html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/formulacalculationoptions/help#@@nowidget@@
Thanks that did the trick. Sorry to have bother you.