https://bugs.documentfoundation.org/show_bug.cgi?id=153506
--- Comment #14 from [email protected] --- (In reply to m.a.riosv from comment #13) > (In reply to bwilderhoo from comment #12) > > ....... In the provided sample data you see that there is no references > > to $Budget.$B24 in the single combined cell range CF. This info has been > > lost in the Manage Conditional Formatting dialog. > > ..... > > I explain to you that in comment#6. Added new expense to the budget in between cell phone and electricity to test theory that missing $Budget.$B24 from the Manage Conditional Format is not a problem for proper conditional formatting of the monthly electric charges in new sheet 2023, as implied in comment 6. Notice in this slightly modified spreadsheet that Feb electricity is incorrectly highlighted as yellow in the new 2023 sheet, but not in the original yyyy spreadsheet. And reviewing the Manage Conditional Formatting dialog for both sheets shows that: 1. The condition for range B19:M19 was correctly updated in the yyyy sheet to compare cell value against $Budget.$B25, rather than against the prior value in $Budget.$B24. 2. Whereas, there is no visible change to the single merged condition shown in sheet 2023 Managed Conditional Formatting dialog. In short, the two sheets, one being a full copy of the other, behave differently for conditional formatting. In sheet 2023, which has the merged single conditional format, $Budget.$B24 is treated as $Budget.$B{23+1}, whereas in yyyy $Budget.$B24 is treated as $Budget.$B24. Sheet 2023 does not allow for relative row addressing to be used within a merged range. This is a bug... plain and simple. The correct logic for merging of conditional formats during the copy sheet function is to ensure that only absolute addressing is used in all of the conditions for adjacent ranges that might be merged. If any relative addressing is found, then skip merging for that set of adjacent ranges. I know this would mean a lot less merging would be done, but it would also ensure that sheets copied from other sheets that use relative addressing have logically equivalent conditional formatting that continue to work as intended even when rows are added or deleted resulting in relative address updates. I have reviewed ask.libreoffice.org as well as other bugs that folks have opened related to conditional formatting problems... and my view of this, which seems similar to those echoed by others in some of the bugs, is that conditional formatting has some serious hurdles to overcome. One individual in a bug report indicated that the issue(s) may require structural code changes. I don't know about that, but I do know that inconsistencies in behavior that folks are reporting, similar to what I am reporting in this bug, give at least the appearance of some significant usage model holes. I can imagine many other problems that might be solved by restricting merging to those adjacent ranges that only use absolute addressing throughout. Sorry about rambling on... but thanks for the lively discussions on this matter. Hopefully the info exchange can facilitate resolving some of the outstanding issues folks are having with conditional formatting. -- You are receiving this mail because: You are the assignee for the bug.
