Forum Discussion

David Edey's avatar
David Edey
Copper Contributor
May 11, 2017
Solved

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 alon...
Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    May 12, 2017

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 12, 2017

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.

David Edey's avatar
David Edey
Copper Contributor
May 12, 2017

Thank you, Sergei.

That works perfectly.

 

Resources