Two prior tests (first and second) found that Calc 6.0.3.2 and Excel 2016 would damage data during each other's export/import processes, thus substantially crippling the ability of the cross-platform user to use either app as a substitute for its respective platform.
This third test sought to test a different export/methodology.
After the normal sync process (Google Drive/Grive2), Calc opened the ODS files, for updating in line with normal practice.
Calc then saved the ODS files, for sync in the normal way. Excel on Legolas then opened the amended ODS file.
Calc found no errors in the ODS file exported from Excel. There was no data loss.
Calc's autofilters, also inherited from Excel, were non-responsive. In all cases, the auto-filters needed removing and re-defining.
More problematic was that =VLOOKUP() wrongly produced #N/A in every instance. In the original XLSX, the function calls on a named range. The named range refers to a range of cells in one worksheet; the VLOOKUP function is used in a second worksheet; both worksheets sit in the same workbook.
Calc also uses named ranges, but not apparently in the same way that Excel (and also Google Sheets) uses named ranges. After some fiddling around, it seems that Calc needed the named range to be a different type of range, a database range. Either that, or Calc has a bug in it whereby it wrongly cannot consider a named range to be a... named range.
The screenshots below show the issue. The first image shows the dialogue box of Calc's menu Data > Define Range. It shows clearly a range called "TESTNEWDBASERANGE".
By contrast, using Calc's menu Insert > Named Range or Express, the following dialogue box revealed no such range. It makes no sense.
The list of named ranges available for pasting - which wrongly excluded TESTNEWDBASERANGE - are those defined in Excel in the original XLSX file on Legolas. (In fact, the file originated a few years ago in Excel 2000 in XLS!).
Neither Excel nor Google Sheets distinguish between a database range or a named range. It wouldn't be rational to do so anyway. After a sync/grive, Legolas' Excel opened the updated ODS to reveal that the new named range TESTNEWDBASERANGE simply isn't available to the user, as per the screenshot below.
Excel did recognise the direct references to the cells underlying the inaccessible named range.
The sole workaround was to replace the named range in the VLOOKUP function with a direct reference to the named range. The workbook thus regained the same functionality in Calc/Gandalf as it has in Excel/Legolas.
As a consequence, the workbooks (XLSX and ODS) were now different datasets, even though the data outcome was the same. Neither machine could properly share the same data (irrespective of whether Excel/Calc disagreed about filepaths of linked files, see test two).
Thus, the user needed to intervene, manually copying incremental data from the ODS file to the XLSX file, then re-saving the XLSX file as an ODS file, replacing the original ODS file. The XLSX/Excel/Legolas/Window file thus became the master file - shared with other users who also use Excel - and the ODS/Calc/Gandalf/Linux file became the secondary, or cache-data-entry, file.
Incidentally, in Calc on Gandalf, the creation of a second database range caused Calc to crash. Calc then sought to recover data, which it did successfully.
Two days later, Gandalf's Calc opened the grived XLSX file (the primary/master data) and immediately saved it has an ODS file, thus creating a ODS copy for temporary/cached data entry. For no obvious reason, and inconsistent with prior tests, the autofilters worked without needing re-defining and VLOOKUP was able to use the named range, as defined in Excel, as it should be!
All the same, the apparent difference between the "named range" and the "database range" was worth a query with the LibreOffice community. There must be some reason why Calc does what it does.
This third test sought to test a different export/methodology.
Environment & required functionality
Excel and Calc both need to be used on the following machines:- The Linux Mint Xfce 18.3 laptop "Gandalf";
- The Windows 10 laptop "Legolas";
- Potentially another Windows 10 machine, name withheld to protect the guilty.
Actions
On Legolas, the two files were opened and updated in line with normal practice. This time, Excel saved a backup copy in format ODS.After the normal sync process (Google Drive/Grive2), Calc opened the ODS files, for updating in line with normal practice.
Calc then saved the ODS files, for sync in the normal way. Excel on Legolas then opened the amended ODS file.
Findings
As with the prior tests, updating data in Calc on Gandalf was broadly fine, although the lack of fully-keyboard-accessible options in the bookmark menu (key F5) proved an unjustified obstacle.Calc found no errors in the ODS file exported from Excel. There was no data loss.
Calc's autofilters, also inherited from Excel, were non-responsive. In all cases, the auto-filters needed removing and re-defining.
More problematic was that =VLOOKUP() wrongly produced #N/A in every instance. In the original XLSX, the function calls on a named range. The named range refers to a range of cells in one worksheet; the VLOOKUP function is used in a second worksheet; both worksheets sit in the same workbook.
Calc also uses named ranges, but not apparently in the same way that Excel (and also Google Sheets) uses named ranges. After some fiddling around, it seems that Calc needed the named range to be a different type of range, a database range. Either that, or Calc has a bug in it whereby it wrongly cannot consider a named range to be a... named range.
The screenshots below show the issue. The first image shows the dialogue box of Calc's menu Data > Define Range. It shows clearly a range called "TESTNEWDBASERANGE".
By contrast, using Calc's menu Insert > Named Range or Express, the following dialogue box revealed no such range. It makes no sense.
The list of named ranges available for pasting - which wrongly excluded TESTNEWDBASERANGE - are those defined in Excel in the original XLSX file on Legolas. (In fact, the file originated a few years ago in Excel 2000 in XLS!).
Neither Excel nor Google Sheets distinguish between a database range or a named range. It wouldn't be rational to do so anyway. After a sync/grive, Legolas' Excel opened the updated ODS to reveal that the new named range TESTNEWDBASERANGE simply isn't available to the user, as per the screenshot below.
Excel did recognise the direct references to the cells underlying the inaccessible named range.
The sole workaround was to replace the named range in the VLOOKUP function with a direct reference to the named range. The workbook thus regained the same functionality in Calc/Gandalf as it has in Excel/Legolas.
As a consequence, the workbooks (XLSX and ODS) were now different datasets, even though the data outcome was the same. Neither machine could properly share the same data (irrespective of whether Excel/Calc disagreed about filepaths of linked files, see test two).
Thus, the user needed to intervene, manually copying incremental data from the ODS file to the XLSX file, then re-saving the XLSX file as an ODS file, replacing the original ODS file. The XLSX/Excel/Legolas/Window file thus became the master file - shared with other users who also use Excel - and the ODS/Calc/Gandalf/Linux file became the secondary, or cache-data-entry, file.
Incidentally, in Calc on Gandalf, the creation of a second database range caused Calc to crash. Calc then sought to recover data, which it did successfully.
Two days later, Gandalf's Calc opened the grived XLSX file (the primary/master data) and immediately saved it has an ODS file, thus creating a ODS copy for temporary/cached data entry. For no obvious reason, and inconsistent with prior tests, the autofilters worked without needing re-defining and VLOOKUP was able to use the named range, as defined in Excel, as it should be!
Conclusion
Inconclusive: more testing required.All the same, the apparent difference between the "named range" and the "database range" was worth a query with the LibreOffice community. There must be some reason why Calc does what it does.
Alternatives
It would be inappropriate to consider alternatives at this time.
Completed Jun2018.
Comments
Post a Comment