3

I have a spreadsheet like this in LibreOffice

A      | B        | C       | D
-------+----------+---------+---------
1      | A        | 1       | B
2      | C        | 2       | C
3      | D        | 4       | D

What I want is that, the background of column A and C will be either green or red if the content is equal or not equal, respectively. On the example above, cell A1, C1, A2, C1 will be in green while A3 and C3 is red because the content is different.

So far I did something like this using Conditional Formatting.

enter image description here

It works for the first row. But when I tried to drag the cell to the cells below, the content of the cells changed to the original cell.

And when I tried to Paste Special and paste only the formatting, the format will try to compare the cell contents against cell A1.

2 Answers 2

3

Using the dollar signs treats the formula as absolute, in that, $A$1 will always look at cell A1 regardless of where you try and drag the formula to. Using $A1 will enforce it always looking at the column A, but use a variable row. Using A$1 will allow for variable columns, but always row 1. Finally, using A1 means nothing is fixed, and the rows and columns will change to suit.

2
  • Thanks! Another question, how come when I delete the cell, let's say C3, and the cell below, (previously) C4, becomes C3. The reference in the formula still go to A4 instead of A3? Jan 2, 2016 at 9:32
  • I'd say that moving the formulas around by inserting/deleting cells shouldn't really change the cell they reference (In your example, the formulas are uniform going down, but it shouldn't assume that). For example, if you had a totals field on an invoice, inserting or deleting a cell above it shouldn't change the fields it's referencing for its totals, and if it did you may not notice it, creating problems. I believe if you end up moving the rows they reference, it'll update to match though, as you still want those items referenced.
    – Jonno
    Jan 2, 2016 at 9:57
1

Turns out I should use A1 instead of $A$1 and use the Format Painter. It works now.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .