Issue 28589 - Conditional formatting with rangename in formula
Summary: Conditional formatting with rangename in formula
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1.1
Hardware: PC Windows 2000
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
Depends on:
Reported: 2004-05-01 09:55 UTC by dfrench
Modified: 2017-05-20 11:13 UTC (History)
2 users (show)

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

Minimal example of issue with comments (6.23 KB, application/vnd.sun.xml.calc)
2004-05-15 01:23 UTC, dfrench
no flags Details
Updated testcase for conditional formatting -named ranges (11.36 KB, application/vnd.oasis.opendocument.spreadsheet)
2008-04-15 22:04 UTC, dfrench
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description dfrench 2004-05-01 09:55:47 UTC
IF a cell range name is used in a formula condition for conditional formatting 
AND the condition is triggered without the cell value itself changing
THEN the slected format is not applied until a repaint is forced ( for example 
scroll off the screen and back).

To demonstrate:
1. Put value 2 in cell B2
2. Give cell B2 the rangename Testvalue
3. Put value 4 in cell C2
Comment 1 dfrench 2004-05-01 10:03:07 UTC
to continue...
4. Apply conditional formatting to C2 with formala is C2>Testvalue ... style 
will be applied
5. Change value of cell B2 to 6 .... style of C2 is unchanged *ERROR*
6. Switch to another window and back (in windows) style is applied
OR 6. Scroll Cell C2 off visible page and back style is applied
Comment 2 flibby05 2004-05-01 16:42:10 UTC
hi, thanks for reporting this!

>> 5. Change value of cell B2 to 6 .... style of C2 is unchanged *ERROR*

hmm, before changing the value in B2 the value in C2 was printed bold, after
chaning B2 to 6 it is not printed bold any longer. However the cell is still
larger than before (I am using "Heading" as the result of the conditional format
statement.) Is this behaviour the "ERROR" you are talking about or do you mean
something else?

I am using the 1.1.2RC build.
Comment 3 dfrench 2004-05-01 22:19:57 UTC
Thanks for the attention to this, Max.
The comment *ERROR* was just to indicate where I thought the action was not 
what I expected, while including all the steps necessary to demonstrate the 
defect. My version is 1.1.1RC3 which I believe is unchanged at 1.1.1 (according 
to the MD5SUMS)
Comment 4 dfrench 2004-05-01 23:46:22 UTC
My guess is that the modify listener is not set up for the range name.
I suggest that any fix takes into account the fact that the range_name itself 
may be pointed at another cell and the condition should be re-evaluated then. 
That seems harder to resolve as the modify listeners are attached to the cell 
range and not the range name. That is, ScCellObj has  addModifyListener but 
ScNamedRangeObj does not.
To continue with the  problem/fix demonstration ...
step 7. use insert name define to change cell range for testvalue to C1 (which 
contains nothing) and the cell format *should* change back to default formatting
Comment 5 frank 2004-05-05 08:44:09 UTC

I'm sorry to tell you but I could not reproduce your problem. I've checked both
with OOo1.1.1 and OOo1.1.2rc on Windows and Linux.

As you should know, without re-produceable Issues we can't fix something. So I
have no choice as to close this Issue as worksforme. Feel free to re-open if you
could reproduce this problem at least on two different machines.

Thanks for your understanding.

Comment 6 frank 2004-05-05 08:44:29 UTC
closed wfm
Comment 7 dfrench 2004-05-14 02:40:12 UTC
Will you please re-open this issue. I have personally identified 2 further 
separate configurations with the issue (clean builds at my local computer store)
OOo V 1.1.0 Windows XP Pro SP1
OOo V 1.1.0 Windows XP 64bit build 3790 SRV03
A fellow user on OOo Forum confirms the existance (for them) at windows XP SP1 
I still consistently get the effect at OOo V1.1.1 rc3 and Windows 2000 Pro

Comment 8 peter.junge 2004-05-14 10:35:16 UTC

Frank is out of office for some days. I'm reopening it without any further look.

Comment 9 peter.junge 2004-05-14 10:37:20 UTC
reassign it to Frank just to force a mail in his intray
Comment 10 dfrench 2004-05-14 22:37:48 UTC
A fellow user on OOo Forum confirms the existance (for them) at OOo V1.1.0 and 
Linux debian build. A fair sprinkling so far but certainly not pointing at a 
particular release of OOo, operating sys, or combination.
Comment 11 dfrench 2004-05-15 01:23:08 UTC
Created attachment 15286 [details]
Minimal example of issue with comments
Comment 12 frank 2004-06-07 13:47:33 UTC
Hi Niklas,

if possible a fix for OOo2.0 would be fine.

It's a repaint problem, CTRL+SHIFT+R will show the correct styles.

Comment 13 niklas.nebel 2004-06-07 19:55:07 UTC
Comment 14 huw 2008-04-14 16:09:44 UTC
Seems fixed for original first six steps, but not for step 7 as described by 
dfrench at
Comment 15 dfrench 2008-04-14 23:08:28 UTC
I can confirm that original issue (step 5) is fixed (thanks team!) but that
moving the definition of the named range does not trigger the necessary  repaint.
Now at version 3.0.0(Beta) Windows 2000 professional
Comment 16 dfrench 2008-04-15 22:02:10 UTC
Perhaps "fixed" was optimistic. There seems to be some eratic or timing issue
around this as well. 
I conducted a test using a 2x2 NamedRange  ... formulas that compared an offset
in this range with a value worked ok (immediately switching styles as expected)
BUT if the value was replaced by a cell reference, a repaint of screen was
needed (forced) to reveal the style change. 
Exercising this test a bit resulted in the problem going away!!! But recreated
simply by copying the conditional formatting cells elsewhere.
I notice that we are coming up for the 4th anniversary of this issue, is there a
party arranged?
Comment 17 dfrench 2008-04-15 22:04:30 UTC
Created attachment 52840 [details]
Updated testcase for conditional formatting -named ranges
Comment 18 Unknown 2010-10-22 19:29:08 UTC
Created attachment 72190
Comment 19 Marcus 2017-05-20 11:13:54 UTC
Reset assigne to the default "".