Apache OpenOffice Community Forum

Hi everyone,

I have a strange behavior of my screen refresh when I use the function INDIRECT in conditional formatting. The purpose of this post is to ask you to test the scenario below and indicate the result you get. You should also report your model of graphic card. If many of you confirm the behavior, I will open a bug.

Scenario
1. Open a blank Calc document
2. Select cell A1 and name it "Rank" (either click on the combo box in the formula bar or use the menu Insert > Name > Define)
3. Write the number 1 in cell A1, the text "Rank" (without quotes) in A2 and the formula =INDIRECT(A2) in cell A3. Cell A3 returns the content of cell A1, which is a normal behavior.
4. In the range D6:D31, fill the cells with random digits, in any order. It is not important to have all the digits as long as most of them are present
5. Select the range D6:D31 and click on the menu Format > Conditional Formatting. Enter the first condition as: "Cell value is" "greater than" "=INDIRECT($A$2)" cell style "Heading1", then click OK. You should notice that the numbers in the range D6:D31 are changing format, which is the expected behavior.
6. Click on cell A1 and change its content to 7. On my machine, the screen is NOT properly refreshed and the numbers below 7 are not formatted back to the default style.
7. Scroll to the right until column D disappears from the screen, then scroll back left to show column D. The cells D6:D31 are now displayed with the proper style.
8. Repeat steps 6 and 7 with different numbers and different ways to refresh the screen (minimize-maximize, move another application window above column D). The most expert of you may try with other styles as well.
9. Copy the range D6:D31 to E6:E31
10. Select the range E6:E31 and click on the menu Format > Conditional Formatting. Edit the first condition as: "Cell value is" "greater than" "=$A$1" cell style "Heading1" (only the formula has changed)
11. Repeat step 6. Now, the screen is properly refreshed!

OS: Windows XP, SP3
OOo: 3.0.0. (build m9)
Graphic card: nVidia GeForce 8600 GT
Result: No screen refresh in step 6

OS: Windows XP, SP3
OOo: 3.0.0. (build m9)
graphic card: Radeon IGP 320 M
Result: No screen refresh in step 6

Thanks in advance for your collaboration!

Confirmed. In my other spreadsheet using this similar process, the configuration I have doesn't cause a problem (because the cells are split into multiple sheet), but I may need to have data on the same sheet, so refreshing would be nice to have.

Hi squenson,

That's a very observant discovery, and I can confirm I am seeing what you are.

Firstly I would assume this is not related to hardware, and definitely a bug (possibly intended or not easily avoided behavior) with Calc. Tbh, I haven't a lot of experience with Calc, but from what I have seen, it doesn't do a great job at refreshing conditional formatting or data filters as would be required (I know with data filters you are expected to manually refresh by pressing 'refresh range' so that is possibly a very separate issue).

When a cell is changed, and all dependent cells updated etc, it Calc should probably run through its list of (on-screen) conditional format filters and update them.

For some reason as you discovered, when a conditional format filter has an absolute reference as its comparison value, this conditional format filter range is refreshed upon change of that cell. Kind of makes sense that this would work, since anything with an absolute reference would be an easy candidate to put in that referenced cell's (A1) "dependent objects to update when I am changed" list.

Yet if the conditional format filter has a relative reference or use of a construct such as INDIRECT() then for unknown reasons they are not making it into the main cell's (A1) "dependent objects to update when I am changed" list. I would have thought the case of relative reference would be simple too (and this would probably be most easy to fix). The case of using INDIRECT() might make things more tricky from the developer point of view due to indirection of the relationship, but hopefully that can be solved too.

The most interesting thing of what you exposed is that if just one conditional format filter range is updated, then this is triggering Calc to traverse it's entire (or just on-screen) list of conditional format filter ranges to update them.

I am glad you pointed this out, because I didn't even know how to update these conditional format filters. So now I have two ways: move off screen temporarily or use an absolute reference.

Definitely would be nice to have this situation cleaned up.

ANYFUNCTION($A$2) gets recalculated when the value in A2 changes or if the function is a volatile one. I belive that INDIRECT is a volatile function thus INDIRECT($A$2) updates as expected. Appending +RAND()*0 [adding a recalculated volatile zero] cures problems like this, but it seems to me that volatility does not work with conditional formatting.
A hard recalculation [Ctrl+Shift+F9] enforces recalculation.
Do you want to pick calculation models from a list box? Try http://www.mediafire.com/download.php?dmx5u4nu4zi

bobban,

Thanks for your comments. As I have noticed during my tests some different behaviors (like this one: only the first four rows in column D are refreshed when I used texts instead of numbers, or this one: only copy the cells D18:D22 to E18:E22 at step 9, then only the cells on the same row are updated!) I thought it would be a problem with the graphic card. In fact, to build on what Villeroy wrote, the content is properly refreshed, it is just the display that is not refreshed, so I guess that Calc proceeds in two step when updating a cell: i) update the content, ii) update the format. It may also just be a problem of timing of refresh on fast processors.

I thought it would be a problem with the graphic card. In fact, to build on what Villeroy wrote, the content is properly refreshed, it is just the display that is not refreshed, so I guess that Calc proceeds in two step when updating a cell: i) update the content, ii) update the format. It may also just be a problem of timing of refresh on fast processors.

hmmmm...what Villeroy explained very well was how content is updated. As he pointed out, if this situation were just a case of formulas using absolute, or relative references, or INDIRECT() etc, then we would not be seeing any funny behavior. In your example A3 contained "INDIRECT(A1)" and there was no problem with that.

The problem was a conditional formatting filter that also had the INDIRECT() formula (or a simple relative reference I noted). There is no content change here (and as Villeroy pointed out, the rules of 'volatility' do seem to apply to formatting). I would suspect that a filter such as this is an object in its own right that needs to be told to update itself. I would have thought a simple relative reference in the filter "=A1" would put this filter object in the A1 cell object's list of things to update when it changes (ie. make filter with "=A1" so tell A1 cell object to put the filter on it's list). I would also have hoped the same for using INDIRECT(), although this is a more complicated situation (it might have to be on A1 and A2 cell object's list etc).

Fixing these things may undesirable to performance, but I wouldn't have thought so.

This will have nothing to do with processor. It's the software behavior imo.

I think it's worth reporting.

Instead of using INDIRECT in conditional formatting, you could try something like
helper_list
Rank 1
Frequency 5
Count 7
...
First column replaces former A1, second column replaces former A1. Bind a range-validation to A2 and A1 =VLOOKUP($A$2;helper_list;2;0).
Conditional format refers to $A$1 rather than INDIRECT($A$2).