Calc - Conditional Formatting Inconsistent

[furgussen]

I’m building a budget spreadsheet. I want to highlight cells that are over budget.

So I have rows with budget categories. At the end of the row is the budget value.

So my condition says if Cell Value is greater than J6 (J6 being the cell with the budget value) then apply a different style.

The cell range is B6:I6.

I’m finding the formatting is random. Let’s say J6 is 20 (all cells are formatted as currency). If I put 12 into B6 it’s fine. I put 11 into C6 and it triggers the condition.

I’ve also tried putting 5 into all cells in row 6 and some highlight, some don’t.

What am I doing wrong?

[mariosv]

Maybe doing J6 absolute → $J$6

[LeroyG]

@furgussen,
As @mariosv wrote, if do you leave J6, B6 compares with J6, but C6 compares with K6, D6 with L6, and so on.
If do you fixes the column J (i.e. $J6), the condition works as do you expect.

[joshua4]

Possibly redundant from LeroyG, but perhaps a clarification for more elaborate situations: Any time you create a conditional format for a selected range of cells: any unfixed reference (like A1) is just a relative reference as it would be from the top left cell of the range to that reference. So in the picture here:

if you now use Format>Conditional then in the Formula Is… use A1, that means A1 when evaluating for the C3 condition, A2 when evaluating for the C4 condition, B1 when evaluating for the D3 condition and B2 when evaluating for the D4 condition.
It’s like if it is a knight’s move from chess for the top left corner to the reference cell, then it means the same knights move for each cell.
Meanwhile, as LeroyG points out, $A$1 literally means just A1 for each and any cell in the conditional range.

[furgussen]

This was it. Thank you!

[furgussen]

Thank you @joshua4 and @LeroyG for the explanations! I didn’t know the conditional matching worked liked that. Makes sense now.