Mass ConditionalFormatting with DefinedNames not working !

[onidarbe]

Hi,

If I add a conditional format to multiple cells and include a defined name then it doesn't work !
It does work when I open "conditional formatting" after this cell-by-cell and without altering anything closing it again. Hard to do if it is on a hole column

It also works using the automatic names like 'ColA' but then one can't insert columns without messing up the formula's = other flow/error in OO

Example:
1 column with label "ColA" with a manual difinedName "ColA" and data 1, 2, 3, 4, 5
Then in selecting other full column I enter a conditionalformat formula coloring the cell red: ColA>3
result: Nothing is red until I open/close "conditional formatting" cell-by-cell....

HEEEEELP Please

PS: Can't upload example, I get: "Sorry, the board attachment quota has been reached."

[onidarbe]

Can anyone conferm me, that it is a bug and let the programmers know?

[Villeroy]

All I can confirm is that conditional formatting works correctly.

[onidarbe]

This time I could upload an example. Plaese have a look...
As soon as you open and close the conditional format on C2, without changing anything: The cell will turn red.
Then open and close the conditinal format on having the total colom C selected: The cell will be white again.

thanks

[TheGurkha]

The problem is you are trying to compare a named range against a single cell value. OOo has no way of knowing that you mean the cell in A3 if you say Test<4 in the conditional formatting for C3. You need to use constructs like A3<4 in the conditional formatting dialog.

On reflection, what I've written here is cobblers.

[onidarbe]

NOT CORRECT:
I'm using the defined name ("Insert/Names/Define...") to name a full column-range. In this case "TEST" to $Sheet1.$A$1:$A$65536
Then using the word "TEST" in the conditional format as "TEST<4". Works well in adding it cell-by-cell not applied on full column at ones !?

is working: Inserting a conditional format, "TEST<4" on each cell in column C, if inserted cell-by-cell.
To much work on 65536 cells...

ain't working: Inserting a conditional format, "TEST<4" on the first cell while the full column C is selected.
Though working when after adding it to the full column, open and close each cells conditional format whitout changing anything !????
? Xray doesn't seem to show anything that has changed. ?

is working: Inserting a conditional format, "A1<4" on the first cell while the full column C is selected.
But this last one has the long known bug, that one can't insert/delete any more columns without messing up the references in the conditional format formulas. Even not when using "$A1". That's why I'm trying to use defined range-names now. But it seems these have there own bugs as well

[onidarbe]

Villeroy, TheGurkha,
Do you understand now where I see the bug?

[FJCC]

I believe I reproduced the behavior you see. However, I did not see a bug when I selected C2:C65536 and set the conditional format to $A2 < 4. I then inserted a column between A and C and the conditional formatting was preserved. The formatting was not preserved if the conditional format was A2<4. I'm using OOo 3.1. I hope I'm not just sowing confusion.

[Villeroy]

Now I see clearly what you mean. A reference to an entire column evaluates to the intersection with the current cell's row with the referenced column. Otherwise many of the named references would not work as expected.
With named references colA and colB referring to $A$1:$A$99 and $B$1:$B$99 respectively:
C1 =colA+colB adds the respective values in the same row as C1. This is a freqently used way to use names.

Since names are nothing but aliases, the same works without the names as well:
C1 =$A$1:$A$99 + $B$1:$B$99. It is equivalent to $A1+$B1 (this row's value in A plus this row's value in B)


This "mechanism" which "pulls over" values from the same row seems to be unimplemented for conditional formatting.
But a reference to a single cell $A1 works as expected. The relative row reference should point to the same row as the active cell when loading the dialog.
Say we have selected entire rows 1 to 99 with the input cell anywhere in row 99, and we want a conditional formatting if column A is greater than 3. The correct formula would be $A99>3 (if this row's value in absolute column A is greater than 3, for all selected rows likewise).

Edit: Filed issue http://www.openoffice.org/issues/show_bug.cgi?id=102084

[onidarbe]

Villeroy: Thanks for reporting this bug. Though maybe you should add that it works if the conditional-format "DefNameColA>5" is applied to 1 cell, but not if applied to multiple cells.

FJCC: Say ColA=values 1...10. There are now 4 posible things that could happen,

Using a conditional-format-formula in ColB: "$A1>5":
1) Inserting a column before ColA. The "$A1>5" refers to the wrong column.
2) Inserting a column before ColB. The "$A1>5" reference is correct.

Using a conditional-format-formula in ColB: "A1>5": (without the $)
3) Inserting a column before ColA. The "A1>5" now "B1>5" reference is correct.
4) Inserting a column before ColB. The "A1>5" now "B1>5" refers to the wrong column !!!

The solution for this bug would be when not using "$", the reference should change only if the reference shifts and not the range where the formula is located.
Maybe Villeroy can add this "Filed issue" again, with more explication so maybe after 3 versions of OO this bug will finally be solved

So I thought the solution for now would be using DefinedNames, only they seem to have there own bug
I've been trying and trying to use OO instead of Excel because the firm I'm working only has OO, but it seems I always hit a bug...

[Villeroy]

The issues with static references on row/col-insertion are already known.
You could set any cell in row #1 as active input cell and define ColA as $A1 and ColB as $B1 (absolute column, row relative to the active cell).
These names will refer to the single cell in the respective column of the same row.

I'll move this topic from the API forum to the Calc forum.

[onidarbe]

Villeroy, I don't understand, I'm lost. Are you giving a solution? What is or how do I "set as active input cell" ?
Even with Defining ColA as $A1 doesn't solve it.

The main question is: How do I insert conditional format formula's in a full column, whitout having the references being messed up when inserting columns?!
PS: The "static references on row/col-insertion" issue is old and not even solved from OO version 2 to 3. I was asking if you could kick this issue a bit , now that even using DefNames can't solve it with the that issue.

[TheGurkha]

I was asking if you could kick this issue a bit

The only thing that can kick an issue is the number of votes for it, but even then, votes are only one of the things taken into consideration when issues are reviewed for including or fixing in a new build.

(As you probably know, we're not the developers.)

[Villeroy]

The main question is: How do I insert conditional format formula's in a full column, whitout having the references being messed up when inserting columns?!

It's not possible because the cond. formatting does not behave like the cells do. The reference to a row or column does not shift with the original cells when you insert cells. X1 becomes Y1 when you insert a column before X. References in c.f. do not follow their original references. The references are static, which is a known issue, but a differnt one than your original issue in this thread.

Using labels rather than named refs could be one solution. Row and column labels provide an alternative to reference rows and columns by a name (menu:Insert>Names>Label...). The name is specified by a header row or column. They don't have this problem as long as the headers move with their data. But don't change the labels then.
=SUM('Amount') always refers to the row or column with a leading text "Amount".

[onidarbe]

Villeroy, again thanks for trying to help me!

I've tried the labels, but it's the same problem as hard-references. If I insert a column in between the labled-data ColA and ColB, the formula becomes "#NAME?B$1>5" !!! Being ColA: values 1....10, ColB: ConditinalFormat-formula " 'ColA'>5 "

So again no solution to overcome the bugs with conditional-formatting

Seems I need to write code to auto-change all conditional-format-references as soon as colomns shift

[Villeroy]

Right, labels can solve the first problem (cell value < 'Labeled Column') but not this one. Formulas with labels adjust well to insertions and deletions but c.f. becomes invalid.

Wow, this one is 7 years old: http://qa.openoffice.org/issues/show_bug.cgi?id=4155

[onidarbe]

I'm sorry Villeroy, I don't understand why you write "Right, labels can solve the first problem". Maybe it's because my mother-language ain't English.

Nothing is solved here !!!

BUG 1: Conditional-format-formulas get messed up on inserting a column when using absolute and/or relative references !!!
BUG 2: Conditional-format-formulas can't be entered on more then 1 cell at the time using column-labels and/or defined names !!!

And yes 7 years is what happens if you need more votes on an issue. They get forgotten. People first need to use it, then be shure it's a bug, wanted to invest time to explain and test it further, find where to vote for it and finally vote for it . Isn't it time they do something about it?

I don't even know where to vote for this, or I would have voted on it for 100 days by now. I'm still trying to write what would take only some days in Excel, because the firm where I work don't want to buy Excel to help my administration on a selling-job. So where do I vote?

So now I'm writing OO-Basic code to refresh the conditional-formats on inserting/deleting columns.... Can't use columnlabels nor definednames and I need to relocate the absolute/relative references each time.

What ever I'm trying to write in OObasic I get bugs bugs bugs , not even telling that de code is much longer and less obvious, compared to Excel-macro's.

[Villeroy]

Get a new sheet
Put some values in A1:A5
Select B1:B5 with B1 being the active input cell (the active cell wiithin the selction matters!)
Conditional formatting of B1: CellValue=$A1 --> Some_Back_Colour
Insert a new row.
The new cell in B gets coloured because both A and B are empty.
The other cells work as expected too.

Bug #1
Insert a column in B and everything keeps on working.
Insert a column in A and things go wrong because the c.f reference to column A remains the same. A formula would adjust the reference to the same values, which is B now.
It makes no difference if the reference is named. CellValue>ColA has the same problem when you insert into or before the named column.

Bug #2 (the new one I've filed)
A formula in cell B1 =A2:A5 returns #VALUE since one cell can not show more than one value and the referenced vector has no intersecting row nor column with B1.
A formula in cell B1 =A1:A5 does not return #VALUE. It picks the respective value from the same row. The conditional formatting does not do that trick. It can not fetch the parallel value from the referenced vector.
It makes no difference if the reference is named. CellValue>ColA has the same problem if ColA refers to more than one cell.