Libreoffice won't save sheetreferences readable by Excel

GreenBanana1 · October 1, 2024, 11:41am

Hi All

I’m using LibreOffice, Thank you. And I have to share a spreadsheet with Excel users.
The Excel users see functions with an extra ‘@’ sign. After removing the sign it works.

Has anyone experienced this before?
What should we do?

I know a solution is for me to use Excel, But I don’t want to.

Below function looks up a value(E2) in another sheet, then returns the value in column(C) of the matched row in the sheet(other_sheet).

=IF(ISBLANK($E2),"",INDEX($other_sheet.$C$2:$C$999,MATCH(1,($E2=$other_sheet.$A$2:$A$999),0)))

It works for me in libreoffice. But Excel users see a different function when they open the file. I tried sending .xlsx and .ods

=IF(ISBLANK($E2),"",INDEX(@other_sheet.$C$2:$C$999,MATCH(1,($E2=@other_sheet.$A$2:$A$999),0)))

Then they get a function which shows nothing.

sokol92 · October 1, 2024, 12:00pm

Let’s check together.

In LibreOffice, in a new book, create Sheet1 and otherSheet.
In cell A1 of Sheet1, enter your formula:

=IF(ISBLANK($E2);"";INDEX($other_sheet.$C$2:$C$999;MATCH(1;($E2=$other_sheet.$A$2:$A$999);0)))

Save the file as Example.ods (attached).
Open the file in Excel (my version is Excel 2016). The sheet name in the formula is shown normally.

Example.ods (7.8 KB)

sokol92 · October 1, 2024, 12:34pm

Another aspect of the problem is that if we plan to share the workbook with Excel users, we need to take into account that Excel has a logical data type, but Calc does not. Therefore, it is better to write your formula as an array formula (enter Ctrl+Shift+Enter):

=IF(ISBLANK($E2);"";INDEX($other_sheet.$C$2:$C$999;MATCH(TRUE();($E2=$other_sheet.$A$2:$A$999);0)))

In this form, the formula should work in both Calc and Excel.

GreenBanana1 · October 1, 2024, 12:54pm

I’ll give it a try
thanks
Example.ods (14.7 KB)

GreenBanana1 · October 1, 2024, 1:00pm

After replacing ‘1’ by ‘TRUE()’
it kept working in libreoffice, but it doesn’t work in Excel

It says:
Error: value not available (translated, the borrowed Excel is in Dutch)

edit:
Retrying with saving with ctrl+shift+enter

GreenBanana1 · October 1, 2024, 12:52pm

You’re right the @ problem isn’t currently showing anymore.
Probably fixed since I switched the file to .ods.

I’ve done some extra test.
Example.xlsx (7.5 KB)
Example.ods (14.7 KB)

I’ve saved and opened both(.ods & .xlsx) in libreoffice and excel

When reopening the .xlsx file in libreoffice it automatically places a $ in the function before the sheetname, if there isn’t one. This is an observation. It doesn’t seem to matter.

I have found a problem with the match function.

# This works: libreoffice.ods & libreoffice.xlsx & excel.ods & excel.xlsx
=IF(ISBLANK($C3),"",INDEX($other_sheet.$D$2:$D$1000,MATCH($C3,$other_sheet.$C$2:$C$1000,0)))

# This works: libreoffice.ods & libreoffice.xlsx
=IF(ISBLANK($C3),"",INDEX($other_sheet.$D$2:$D$1000,MATCH(1,($C3=$other_sheet.$C$2:$C$1000),0)))

# This works: libreoffice.ods & libreoffice.xlsx
=IF(ISBLANK($C3),"",INDEX($other_sheet.$D$2:$D$1000,MATCH(1,($C3=$other_sheet.$C$2:$C$1000)*($B3=$other_sheet.$F$2:$F$1000),0)))

Libreoffice works, Excel doesn’t

I think I have to find a way to write the double match in a different way, and keep it working on libreoffice while also supporting excel.