Forum Discussion
Conditional formatting with a complex formula
I'm attempting to highlight a table of data queried from an incident tracking database using conditional formatting to set the background to one of three colours red, green or yellow or leave it alone.
If date in the deadline column has passed, the rows should be
- green, if the status is a resolved status
- red, if the status is an unresolved status
- unformatted, if the status is pending
If the date in the deadline column hasn't passed but is withing two day,
- yellow if the status is an unresolved status
- unformatted for all other statuses.
So, I've constructed a formula in an extra column that evaluates as TRUE for the rows I want green:
=AND(VLOOKUP(INDIRECT("J"&ROW()),Resolved,2,FALSE)="Unresolved",(INDIRECT("I"&ROW()) < NOW()))
The VLOOKUP function checks the J column against a list of statuses that are considered resolved.
I have used the INDIRECT functions so that when the conditional formating is applied to the query results table, the whole row will be formatted.
Now comes the problem...
When I paste this formula into the conditional formatting, nothing happens...
What am I doing wrong?
Any suggestions.
I've attached a redacted sample of the report.
Hi David,
I don't know why but both AND() and OR() don't work within conditional formatting rules if there is INDIRECT used inside. Not sure that's bug or some logic behind, just took that as the fact.
At the same time exactly the same formula within the cell returns correct result.
If re-write your formula as
=(VLOOKUP(INDIRECT("J"&ROW()),Resolved,2,FALSE)="Unresolved")*(INDIRECT("I"&ROW()) < NOW())which is the equivalent of AND, it shall work with conditional formatting.
I didn't check the formula itself, Detlef suggested another variant without INDIRECT.
Hello David
I'm not sure why your formula doesn't work but this formula does:
=AND(VLOOKUP($J6,Resolved,2,FALSE)="Unresolved",$I6<NOW()))
Hi David,
I don't know why but both AND() and OR() don't work within conditional formatting rules if there is INDIRECT used inside. Not sure that's bug or some logic behind, just took that as the fact.
At the same time exactly the same formula within the cell returns correct result.
If re-write your formula as
=(VLOOKUP(INDIRECT("J"&ROW()),Resolved,2,FALSE)="Unresolved")*(INDIRECT("I"&ROW()) < NOW())which is the equivalent of AND, it shall work with conditional formatting.
I didn't check the formula itself, Detlef suggested another variant without INDIRECT.
Thank you, Sergei.
That works perfectly.Hi there,
I am trying to write a logical formula to ease some of my work.
=IF(C2>12,0,IF(AND(C2<12,C2>11,(C2-B2)<1),1))
I couldn't understand what is wrong with this.
It keeps on giving error, could you help me on this?
Hi Tuncay,
That is separate question, much better if you start new conversation.
Anyway, in general nothing is wrong with your formula, just copy/paste it into worksheet
