Apache OpenOffice (AOO) Bugzilla – Issue 95868
calc 'save as' excel .xls does not change sheet references correctly
Last modified: 2014-01-07 18:44:54 UTC
I exported a .ods file with a field containing =indirect(""'sheet2'"."& "F"&COLUMN()-4) It got saved as a .xls file and read in a german Excel installation (2003) =indirekt("'sheet2'"."& "F"&SPALTE()-4) The '.' needs to be a '!' for this reference to work using the sheet name. Not sure what the fix is here, as it's inside a string and probably difficult to find and handle correctly... but this is an incompatibility to Excel, and could be fixed by allowing '!' as a sheet reference as well as '.' I'd be happy to make using '!' for sheet reference a feature request to solve this issue... otherwise, the only solution is to manually rework the spreadsheets (ugly!).
From Help: "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)."
Automatic translation is appropriate.