[Solved] LibreOffice Calc: Conditional Formatting

[ilsm]

Hi

I have a spreadsheet containing rows of individual records

Cells in Column F show the current status of each particular record (row), and this has to be input manually. The only entries allowed are, "Pending", "In Progress", "Approved", "Invoice Issued" and "Closed". A record should be marked "Closed" once the invoice is paid. (Reports are produced which depend upon the data in Column F being correctly updated.)

Column N shows the amount outstanding on the invoice (by deducting the amount paid from the invoice total). If the amount outstanding is zero, then "Paid in full" is shown.

I want to format cells in Column F so that, if they do not show "Closed" when the corresponding cell in Column N shows "Paid in full", then they will turn red. How do I achieve this?

(Better still would be for the cells in Column F to change to "Closed" dynamically, but perhaps that's being too hopeful.)

Thanks,
ilsm

[Zizi64]

You need create a Cell style with the desired properties (background color, and/or font color) for the highlighting the non-closed records. (You can use one of the predefined colored styles, like the Cell Style "Bad" or " Error".

Then you can apply the conditional format feature (CF) by usage the Format - Conditional... menu item for the records. The condition must be a Formula:
$F2<>"Closed"
(I suppose that the first record is located in the row 2 because the table has a header row)

My sample file was created in my Libreoffice 6.1.6. The recent LO versions handle the CF feature differently than thew AOO can handle it.

ConditionalFormat_and_DataValidity.ods

(15.34 KiB) Downloaded 149 times

[ilsm]

Thanks for the work you have done to explain this to me. It is very helpful, but it looks like the highlighting depends upon the value in only one cell. I need this to happen when the values of two cells conflict, i.e, when the record appears to show that the invoice is paid in full (column N in my spreadsheet), but that the record's status does not show that it is closed (Column F).

The sample file you created highlights all cells in a row where the cell in Column F is marked "Closed". Is there a way for this to happen only when (using row 7 of your file) cell E7 EQUALS "Paid in full" AND cell F7 does NOT EQUAL "Closed"?

Thanks again.

[mikele]

Hi,

E7 EQUALS "Paid in full" AND cell F7 does NOT EQUAL "Closed"?

translatetd into functions

Code: Select all

AND(E7="Paid in full";F7<>"Closed")

[ilsm]

Köszönöm, Tibor: danke, Mikele. That solves my pproblem.