I work for a holistically managed ranch that is developing an apprenticeship program to help train the next generation of regenerative land managers in consistently achieving desirable ecological, social and economic outcomes. To track and guide the learning and mentoring process, we have developed a skills evaluation spreadsheet in LibreOffice.
Unfortunately, the spreadsheet also has to work with Excel, because that’s what the rest of the ranch team uses and what contacts outside the ranch also use. It does, mostly, with the following issues:
- Conditional formatting is mostly gone. I have two sets of conditional formatting:
a. color-code score columns from lowest score to highest score, yellow to green, to quickly see what is above or below average for that particular set of skills.
b. highlight comments columns in red whenever a discrepancy of 1 or more exists between the mentor’s evaluation and apprentice’s self-evaluation.
The “a” formats disappear, while the “b” formats remain when opening and/or saving an .ods file in Excel. According to Is there a way to preserve conditional formatting created in LibreOffice .xlsx and opened in Excel? Thanks - #5 by Phoinx the way to preserve conditional formatting is to create the formatting in Excel first, rather than in LibreOffice.
- Opening the .ods file in Excel appears to mangle the hyperlinks. These are simply internal references between sheets in the same file to enhance navigation, so I would think that would be a non-issue, but they are listed as “invalid” in Excel, and when the file is saved in Excel and is opened again in LibreOffice, the hyperlinks no longer work.
The original file (link works)
=HYPERLINK("#'Workplace Ethics & Etiquette'.A1", $'Workplace Ethics & Etiquette'.A1)
When opened and saved in Excel, it becomes:
=HYPERLINK("#'Workplace Ethics & Etiquette'.A1", 'Workplace_Ethics_&_Etiquette'.A1)
But then doesn’t work in either program.
It appears that Excel changes the names of the sheets themselves, adding the underscore to each sheet. I’m not sure why the link no longer works in either program after opening and saving in Excel, though.
According to here: LibreOffice Calc cell worksheet references that also work in Excel? - Stack Overflow
The way to ensure that hyperlinks work is to change the Tools->Options->Libreoffice Calc->Formula->Formula Options->Formula Syntax to “Excel A1”
My questions are:
- Is any of this behavior a bug that should be reported or that already exists? Is it because MS Office isn’t fully compliant with the ODS format? Or is this some other issue(s)?
- Is there a standard or best practice protocol for maximizing compatibility when developing a spreadsheet to work with both Excel and LibreOffice?
- Are there any potential repercussions or adverse effects I should be aware of to the above-mentioned workarounds, ie., of using “Excel A1” formula syntax to preserve worksheet references and hyperlinks and doing the conditional formatting in Excel so that both programs can preserve conditional formatting?