Libreoffice uses a dot ('.') to reference cells on other sheets, e.g. 'Sheet 1'.A1 Excel does not support this syntax, but instead uses an exclamation mark ('!') like so: 'Sheet 1'!A1 This means documents produced in either system which reference other cells are incompatible. This enhancement request is for LibreOffice Calc to support both syntaxes to improve portability of documents.
Excel syntax is provided as a Formula Option Tools -> Options -> LibreOffice Calc -> Formula -> Formula Options -> Formula Syntax and choose 'Excel A1' for reference you prefer. =-ref-= https://help.libreoffice.org/7.0/en-US/text/shared/optionen/01060900.html?&DbPAR=WRITER&System=WIN
Hmmm, I think the reason I thought this didn't work was because it doesn't seem to work for references in an INDIRECT statement. With the setting 'Excel A1' enabled, ='Sheet 1'!A1 works, but =INDIRECT("'Sheet 1'!A1") does not, but =INDIRECT("'Sheet 1'.A1") does actually still work
Created attachment 159107 [details] Calc document demonstrating the issue
Have you set up properly, Menu/Tools/Options/LibreOffice calc/Formula - Detailed calculation settings - Reference syntax for string references.
No, indeed I haven't, in my defence, this is a pretty obscure setting! With this setting, it works, I note that this setting has an option 'Calc A1 | Excel A1' which worked for me and made all versions work in the example I attached earlier. It would be nice if this was the defualt, if there's no major technical reason not to. It would also be nice there was such an option in the other option (Tools -> Options -> LibreOffice Calc -> Formula -> Formula Options -> Formula Syntax) and that this option was the default setting. My final change to this report will be to switch it back to an enhancement request, for this.
p.s. thanks everyone for your patience.