Formulas not transferring from Excel to OnlyOffice correctly

Beginners
1

Continuing the discussion from How to download my Win10 to an SSD to run it from there?:

For most spreadsheets, moving to LibreOffice or Only Office has been easy. But what is keeping me from going full-on Linux is a spreadsheet issue I’ve not been able to solve.

LibreOffice version 7.6.6.3

OnlyOffice version 7.5.1.23

On Windows 10

We have a workbook that has several tabs, many pivot tables, and some complex formulas that are used like a database. A CSV file gets put into it and filters into a bunch of different functions, several sheets and pivot tables. So far, my attempts to open it into either LO or OO has left me with broken formulas, or formulas that don’t translate right. The guy who developed this is amazing at Excel on a Mac. He knows nothing about LO or OO.

If I could find someone who knows how to translate the formulas, that would be awesome! I did find that when I opened it in OO, some code was inserted into the formulas that aren’t in the E formulas. That was on only one tab. I haven’t checked through all of them.

I figured I’d have to continue with Win10 until I solve this. Maybe run them both off an external SSD until I can get away from Gates?

Any input would be greatly appreciated. I’ve been looking in documentation, but so far haven’t found what I need, so I thought I’d ask and see if anyone on here knows something.

2

I have recently switched to LibreOffice on Linux from office on windows. Although I’m still learning myself, I’m happy to help if I can as I did a lot of development on Excel and am now attempting to transfer my knowledge to LibreOffice.

3

Hi @MamaSauerkraut .

OnlyOffice for Windows isn’t the same program as OnlyOffice for Linux. They function differently.

What happened when you opened your Excel workbook in OnlyOffice for Linux?

4

Expeirence shows that most any spreadsheet from MS Office can be made to work on OnlyOffice or LibreOffice.

However, complex spreadsheets will take more time to adapt over.

Expecting a complex spreadsheet to be 100% compatible out of the box may not be realistic.

For 99% of home computer users, this won’t be an issue.

As for your complex spreadsheet, there are plenty in the OnlyOffice or LibreOffice communities who would probably be glad to help!

5

@MamaSauerkraut
I’m curious when you’re opening & saving the Excel file in LibreCalc, are you saving as 'Excel 2007-365 (.xlsx) or ODF (.ods)?
I’m thinking of ways to possibly create & run a macro or Python program to compare the formulas, identify the differences & correct or adjust to work properly.
Please reach out if you would like more assistance.

6

I’ve not saved it in ODS. Just opening it breaks some of the formulas and VLOOKUP stuff. I’ve been having issues with LibreOffice crashing on my computer lately, so I’ve been using OnlyOffice to see if that’s any better. I’m still in Win10. These two programs are similar, but different. LO and OO open the files with different things happening. I’ve not been able to pursue this as energetically as I would have liked. LIFE stuff has gotten in the way.

I did try to open the spreadsheet I need in LibreOffice in Linux, but I got an error message that it had too many columns. I didn’t see where any columns were cut, but they brought back #VALUE instead of what should be in there. I’m thinking for now I’ll have to use Win and Excel in a VM, once I get that figured out. I don’t know enough about how VLOOKUP and formulas work.

Example of formula differences taken from one of the columns:
Master> .CapColor formula

excel: =LET(color,VLOOKUP([@[Lineitem name]],tblProducts,4,FALSE),IF(LEN(color)<=0,“”,color))

OO: =LET(_xlpm.color,VLOOKUP(tblMaster[[#This Row],[Lineitem name]],tblProducts,4,FALSE),IF(LEN(_xlpm.color)<=0,“”,_xlpm.color))

LO: =_xlfn.let(_xlpm.color,VLOOKUP(tblMaster[[#This Row],[Lineitem

This is just an example of some of the differences I’ve found. Some formulas work, many do not. There are many columns of formulas that sort for 3 sheets of pivot tables. This workbook has 11 Sheets, and the main sheet “MASTER” has rows A - AM of parsed data from 11 columns pasted from a scrubbed CSV file. It’s an amazing workbook, but it’s not translating well to either LO or OO.

Thanks for any helpful input!

7

It took a minute for me to be able to try that out, but it still had issues when I did. However, I did learn more about being about to run a live boot of LinuxMint and also access files on another flash drive. Nice to know that works, and it seemed pretty easy. I wasn’t able to download OO with the Live boot and get it to work, but I tried it in LibreOffice which is already a part of LM.
Slowly, step by step I’m going to get there!

8

@MamaSauerkraut
Hello. I believe the problem is related to the Excel “LET” function, which allows assigning names to calculation results. That is a newer Excel feature that doesn’t yet have an equivalent in LibreCalc, at least not one that I have found yet.
I’ll keep looking & will let you know if I find something else.
Good luck!

1 Like
9

@MamaSauerkraut , it looks like @mkreft found the problem.

“LET” function is also not in the current version of Linux OO:
OO Let

OO version

10

Ah. Thanks for this! It really helps. I suspected there was something Excel was doing the others weren’t. Looks like VM of win is my best option at this point. Good to know so I don’t spend any more time on this at this point.

Much appreciate your efforts in this @nwarren and @mkreft!

2 Likes