Conditional Formatting > Formula is - Ranges with absolute sheet reference issue

[xxkoes]

When using range with relative sheet reference in Conditional formatting > Formula is (Sheet1.$D$12:$D$17 = 3) - the whole range is evaluated, as expected.
When using range with absolute sheet reference in Conditional formatting > Formula is ($Sheet1.$D$12:$D$17 = 3) - only first cell of that range ($Sheet1.$D$12) is evaluated.

This prevents me from using named database ranges in conditional formatting instead of direct cell references because as far as I know, named database ranges can not be defined with relative sheet references, and if they could it wouldn’t be optimal.

Workaround could potentially be using named ranges (which support relative sheet references) instead of named database ranges, but it complicates things.

I am posting this here in case I overlooked something, but suspect I will have to report it on Bugzilla.

Attached file and a photo to clarify things further:

cf_abs_sheet_ref_issue.ods (19.6 KB)

Version: 24.8.7.2 (X86_64)
OS: Fedora 41, Linux 6.12
Locale: en-US (C.UTF-8); UI: en-US

Formula Syntax: Calc A1
Function Separator: ,
Array Column Separator: ,
Array Row Separator: ;
Other Settings: Default

[mariosv]

Bug 139649 - [1] Wrong condition behavior with cell range and sheet name

[ilmari]

xxkoes: you joined the libreoffice-qa chat yesterday, asking what you should do next to further the resolution of the issue linked by mariosv. As a non-developer, there are many things that you can do to make it easier to fix an issue. Finding relevant code locations is valuable. In many cases, it can be done through a process called binary bisecting, but this particular issue is older than the resources we offer for these types of investigations.

What would help a little bit is testing with the most recent unstable build and leaving a comment to the bug report, sharing the information from the Help - About dialog by clicking the copy button found there next to Version Information. On Linux, one way to get an unstable build is to use an appimage script. As an alternative, you might download and extract the Linux-rpm_deb-x86_64@tb99-TDF-dbg package found on the page listing the most recent unstable builds.

Beyond that, one may contract professional support to fix issues.

Keep in mind that the report is one out of nearly 16 thousand confirmed issues and feature requests. The less time developers need to spend on administrative work in the bug tracker and investigating issues from the ground up, the quicker bugs will get fixed. In that sense, helping with quality assurance tasks in general will further any shared LibreOffice goal you might have, not to mention giving you the skills to analyse issues. I am available for mentoring on this topic and you find my email on the Get Involved page.

[xxkoes]

Thanks for providing this information, I’ll use it when dealing with bugs in the future, but as per mikekaganski this one seems to not actually be a bug (or at least not the one I considered) so I’ll leave it at that.

[mikekaganski]

As I explained in tdf#139649, this is not a bug. You always work with conditional formatting, as if you defined its condition for its topmost-leftmost single cell. You do not refer to ranges in formula there for “by intersection” matching - the “by intersection” rule has nothing to do with conditional formats. You need to pay attention to the correct use of absolute vs. relative addressing there; and again, you need to realize, that you are working there from the point of view of a single cell.

So for CFs in the file you provided:

• For C12:C17, "Formula is $Sheet1.$D$12:$D$17 = 3" becomes "Formula is D12 = 3";
• For F12:F17, "Formula is $Sheet1.$G$12:$G$17 = 2" becomes "Formula is G12 = 2";
• For F12:F17, "Formula is Sheet1.$J$12:$J$17 = 3" becomes "Formula is J12 = 3".

Your formulas must not use absolute addressing there; that is the user error. Your formula can not be “select cell from this absolute range by intersection”; your formula may only be “get cell in the same row, from the next column (i.e., by relative addressing)”. Using absolute addressing, your CF formulas look like “get a fixed cell found by intersecting the top-left of the range”.

And note, that this works the same way in other spreadsheet software, too.
(With the exception, that for Sheet1.$J$12:$J$17 = 3, other software would be more strict, and use absolute addressing rules; that Calc works here as you expected is, IMO, the actual bug.)

As to using with named database ranges - you need to demonstrate the real problem, i.e., where that doesn’t work (your sample didn’t use named database ranges; and only assumed, that the underlying reason of failure is the same); and where using them in CF would be useful and preferrable to direct addresses there (which actual task is solved easier / better using those).

[xxkoes]

As “Sheet1.$J$12:$J$17 = 3” part is the actual bug, I won’t pursue this any futher.

But to clarify, I only bothered with ranges instead of single cells because I noticed that I can use named ranges in conditions of conditional formatting. By extention I also thought I can use named database ranges in those conditions. Later I discovered that some named ranges and all named database ranges actually don’t work, which led me to the root cause that was “$Sheet1.” vs “Sheet1.”.

In my question I chose to focus on the root issue and only mention named database ranges not working as a consequence of that issue. Now there is no point demonstrating how they don’t work as this issue is closed.

As for benefits of using named database ranges, I thought discovered something that would help me make identifying and managing conditional formats a little bit easier in a document with 10+ conditional formats per sheet. Now I see that this is a moot point since CF works as you’ve said from the point of view of a single cell.

Also, thanks for the answer.