Apache OpenOffice Community Forum

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

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.

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.

Hi,

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

translatetd into functions

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