Conditional Formatting and Data Validation

[Priya125]

Hello, 

I need some assistance with the combination of conditional formatting plus data validation in the attached document. I am looking to complete the below items:

• Column F to be highlighted with the conditional formatting I have already set up based on the due date, plus, have the dropdown value of "Yes" to be selected when the conditional formatting rule has been applied. 
• Column I to have the Status dropdown value of "Overdue" be automated once the task has become overdue. 
• Lastly, if possible, for Column F if possible can the conditional formatting ignore blanks if there is nothing populated in Column E?

Thank you so much in advance for any help that can be provided!

[#2 · Accepted solution]

@Priya125 actually the only part possible is the part you qualified with "if possible".  Conditional formatting can NOT change values in the cell it will only change formatting.  That said, you could change the Overdue? and Status columns to be formula based.  The Overdue column can use a formula like:

=IFS([@[Completed?]]="","",AND([@[Completed?]]="No",[@[Due Date]]<TODAY()),"Yes",TRUE,"No")

Which will give you "" if col E is blank and then Yes/No if it is overdue.

Then your conditional formatting can just check for "Yes" in col F.

As for Status, can you define col H as "Time Allocated" so that any time in col H means the difference between "Not Started" and "In progress"; so then

=IFS([@[Completed?]]="","",   [@[Completed?]]="Yes","Complete",    [@[Due Date]]<TODAY(),"Overdue",    [@[Actual Time to Complete (in hours)]]>0,"In progress",   TRUE,"Not Started")

otherwise you would have to use Macro/VBA to do what you are asking.

[#3]

@mtarler thank you VERY much for your help on this! This is exactly the kind of help I was looking for :) I really appreciate it.