Can I link the conditional formatting of one cell to the contents of another?

HI Tim,

To your main question: No.

Conditional formatting rules compare the contents of the cell to be formatted to either fixed value or to the contents of a separate cell. If you can arrange to have those cells contain the same type of value, and arrange a means of maing the vlues equal or unequal using an IF statement, then either of the cells can use a conditional formatting rule that sets the format depending on the contents of the other with respect to its own value.

To your described case:

You have a cell (C3) in which task completion is marked by checking a checkbox.

You have a second cell (D3) that contains the due date for the task's completion.

You have a third cell (E3) which reports the number of days to the due date for that task.

C3 is manually entered by checking the checkbox.

D3 may be the result of a calculation, or may be entered from thekeyboard. The contents MUST be a Date and Time value.

E3 contains the formula below:

=IFERROR(IF(C3,"done",DATEDIF(D3,TODAY(),"D")),"overdue "&DATEDIF(TODAY(),D3,"D")&" days")

E3 may be conditionally formatted using rules such as:

Text begins with ov

Text begins with do

Regards,

Barry

NOTE: No time to test these, so some editing may be necessary.

B

Hi Tim,

Here are some pictures in Numbers 3 that may help get you started. The formula in A1 is =TODAY().

if the due date has passed (ie the due date is before today) then the cell will change colour

This will do that:

As Barry indicated, this will only work if the contents of the cells in C and A1 are Date & Time values.

I want to indicate that if a person assigned to a particular task has completed their task and indicates as such in the appropriate box, then another box on the spreadsheet will blank out (specifically the number of days until the deadline).

Here's one way to achieve that effect:

The formula in D3 is =IF(B3,0,IFERROR(DATEDIF($A$1,C3,"D"),0)).

It inserts a zero if the checkbox in column B is checked (the IF does that) or if the date in Due is before Today (the IFERROR does that because DATEDIF expects the end-date to be after the start-date and will throw off an error if the reverse is true).

Then this Conditional Highlighting rule (I scrolled down and chose "Custom Style" initially out of sight at the bottom of the list and since I chose a font color of white it doesn't display in this screenshot as my format choice) will turn the font white if there is a zero in that cell:

I resorted to DATEDIF because Durations in Conditional Highlighting are buggy and don't seem to work, whereas rules using Numbers do.

The end result is that the values in D (Days until due) will "disappear" if the Done checkbox is checked or if the due date is before today's date. You can change the logic and formatting as needed.

SG