Issue 102084 - Parallel referencing in conditional formatting
Summary: Parallel referencing in conditional formatting
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 3.0.1
Hardware: Unknown All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2009-05-20 09:24 UTC by villeroy
Modified: 2013-02-19 19:37 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description villeroy 2009-05-20 09:25:17 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:
Comment 1 Regina Henschel 2009-05-20 13:46:03 UTC
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.
Comment 2 villeroy 2009-05-20 14:33:42 UTC
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.
Comment 3 Edwin Sharp 2013-02-19 19:37:00 UTC
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.