Apache OpenOffice (AOO) Bugzilla – Issue 102084
Parallel referencing in conditional formatting
Last modified: 2013-02-19 19:37:00 UTC
I don't know how it is called, but there is a mechanism which fetches the parallel values from referenced vectors: C1 =$A$1:$A$99 + $B$1:$B$99 returns the same as $A1+$B1 automatically fetching the respective values from the same row of the referenced vectors. This is how most named ranges use to work. =Price*Count automatically fetches this row's values from the referenced vectors. The same uses to work horizontally as well fetching from same column of a parallel horizontal vector. This is not implemented for the conditional formatting. May affect Excel compatibility. Cell validation can do the trick. Forum discussion: http://user.services.openoffice.org/en/forum/viewtopic.php?f=20&t=18662
I don't know, whether named ranges should work in cell value comparisons, but I will give you two workarounds: (1) Use labels instead of named ranges. Constraint would be, that you always refer to a whole column or row. (2) Do not use "cell value" but "formula" and write for example Price*Count>D1 as comparison, where D1 will change with copy&paste.
Thank you for testing this and providing 2 valid work-arounds. Yes, named references do work in cond. formatting as well as in cell validation. Both features take formulas without leading "=" with quoted strings, unquoted addresses, numbers and names. Commonly used example: cell_value > MAX(other_range) Your work-around with the "formula operator" does work. 'Labels' can do the trick using both methods, formula=('Price'*'Count')>$D2 as well as cell_value > 'Price'*'Count'. The user who initiated the forum thread stumbled upon the fact that the latter approach fails with named references. cell_value > Price*Count fails whereas formula = (Price*Count)>$D2 works as expected, so there is a bug indeed. Without having tried, I'd think that Excel would handle this as expected.
The description given here is not clear to me. However, refering to the link given to the community forum in the last post there: Bug 1 - insert column in A gives background to cells in column C although they are different from those in column B. Bug 2 - no bug. the conditional formatting works when B1=A1:A5. Checked with Rev. 1400866.