Forum Discussion

captainFlow's avatar
captainFlow
Copper Contributor
Oct 20, 2022

conditional formatting doesn't work with complex "IF" statement

=IF(INDIRECT(ADRESS(ROW();COLUMN()))>1;1;0) It doen't work, when I put it in the formula for conditional formatting, but for example when I try to separate it and use "INDIRECT(ADRESS(ROW();COLUMN()...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Oct 20, 2022

Patrick2788 

But wouldn't be much easier to create a rule of type 'Format only cells that contain' with Cell Value, greater than, and =1 ?

 

Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 20, 2022

HansVogelaar 

Yes, definitely much easier. captainFlow mentioned this was for demonstration purposes. Maybe he can share his goal?

  • captainFlow's avatar
    captainFlow
    Copper Contributor
    Oct 20, 2022

    Hi Patrick
    Yes, you are right. Here is full formula I need.

    =IF(INDIRECT(ADRESS(ROW();COLUMN())) > 0; IF((ROW()=1); INDIRECT(ADRESS(ROW();COLUMN())) >= ($D2*1,1); INDIRECT(ADRESS(ROW();COLUMN())) >= INDIRECT(ADRESS(ROW()-1;COLUMN()))*1,0); 0)

    I created the tickets after found out that nested IF statement doesn't work for me.

    UPD. To be more correct, using INDIRECT function inside of IF doesn't work for me.

    UPD.2. The thing is I've written this formula for google sheets for Conditional Formatting and it works fine. I can't find out why it doesn't work for excel in microsoft365

Resources