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.

Tuncay Sahutoglu's avatar
Tuncay Sahutoglu
Copper Contributor
Apr 19, 2018

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?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 19, 2018

    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

     

    • Tuncay Sahutoglu's avatar
      Tuncay Sahutoglu
      Copper Contributor
      Apr 19, 2018

      Hi Sergei,

       

      Strangely, it still gives error.

      I am new at this, how can I solve it?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 19, 2018

        Could you attach a sample file?

Resources