2019-07-03
How to check other column value
stackoverflow
Question

I am trying to do something very general case that is:

I am maintaining my project tasks in Excel, my Excel contains,

No, TASK, START-DATE, END-DATE, DUE-Days, STATUS

example:

1, task1, 01/05/2019, 4/05/2019, -5, Completed

Due-days(-5) =D1-TODAY 

By using conditional format, if due-days is less than 0 then I am making that Due-days cell(D1) background color as Red. Till here I did and working fine.

But what I have to do is, even it's due-days are less than zero, if the task status(F1) is completed. I have to make the background color of D1 as Green.

I need to add one more condition to that conditional format, but I am not able to find any such option. How can I do this in Google Sheets?

Answer
1

green color:

=IF(AND($E1<0,$E1<>"",$F1="Completed"), 1)

red color:

=IF(AND($E1<0,$E1<>""), 1)

enter image description here

How to check other column value
See more ...