Big problem with conditional formatting sheet referencing in xlsx: dot reverts to exclamation point, breaking the reference

[PeteSapai]

I’m trying to highlight any word in column G that appears in sheet1 column A, using:

COUNTIF(sheet1.$A$1:$A$282,G2)
Range: G2:G1000

This seems to work fine at first, however, when closing and reopening the file the . in the formula reverts to a !, consequently breaking the reference.

I can fix this by saving the file as ods, but I prefer not to, as it’s a work thing. Is this a general bug with sheet referencing in xlsx files?

Tested with 2 builds (I use 7.5.5.2 because for some reason v24 cannot open some of my xlsx files that v7 has no problem with):

Version: 7.5.5.2 (X86_64) / LibreOffice Community
Build ID: ca8fe7424262805f223b9a2334bc7181abbcbf5e

and

Version: 24.8.2.1 (X86_64) / LibreOffice Community
Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13

CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-CA (en_SE); UI: en-US
Calc: CL threaded

Edit: the ods and formula are incompatible with Excel: it will open the file, tell you there’s something wrong with it, try to fix it and then delete the formula. It’s a solution so long as noone uses Excel to open the file. (Which someone might, at some point.)
Isn’t there any way to have LO use ! and make it compatible with Excel?

[Steph1]

I cannot reproduce the error. When I reopen the file, I get this formula:
=COUNTIF($Sheet1.$A$1:$A$282,G2)
with an added $, which is normal because a reference to a sheet is always absolute. My version of Calc is exactly the same as yours:

Version: 24.8.2.1 (X86_64) / LibreOffice Community
Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13
CPU threads: 32; OS: Windows 11 X86_64 (10.0 build 26120); UI render: Skia/Raster; VCL: win
**Locale: en-US (en_US); UI: en-US**
Calc: threaded

but the locale is different. Could it be the reason of the problem?

[Villeroy]

ask112778.xlsx (12.7 KB)

[PeteSapai]

Alas, changing locale doesn’t fix it. But I noticed that Excel removes the conditional formatting from the xlsx, too. I’m starting to fear that as long as Calc uses . and Excel uses !, there is a big incompatibility between the 2 to which there might be no solution…

[PeteSapai]

Strange, there’s no problem with this file (except for Excel: it doesn’t understand the formula"=@_xlfn.ORG.LIBREOFFICE.RANDBETWEEN.NV(0;20)").

I guess I’ll have to accept it’s my file and hope it doesn’t happen again (or too often).

Thanks for the help, Villeroy and Steph1!

[Villeroy]

Oh, sorry. Calc knows a volatile and a non-volatile version of RANDBETWEEN. Simply replace it with RANDBTEWEEN or paste-special constant values over the formula cells.

[PeteSapai]

No problem, it was just a test file, no need for a working RANDBETWEEN

[Villeroy]

In order to analyze your problem, a demo file from your side would help

[PeteSapai]

Thanks, but I think I’m going to have to give up on this. I tried removing some sensitive data/sheets, but somewhere along the road that solved the problem with the (now unusable) file. Since I don’t have time for (nor want to) see which step solved it, I guess I’ll just have to be ok with not resolving this. Thanks anyway!