Forum Discussion

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

Patrick2788

Silver Contributor

Oct 20, 2022

captainFlow

This might work. I see you're using semi-colons so I've written the formula using those as the list separator. I believe the issue with the other formula is the use of ADDRESS.

=IF(INDIRECT("RC";0)>1;1;0)

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
  Silver Contributor
  Oct 20, 2022
  HansVogelaar
  Yes, definitely much easier. captainFlow mentioned this was for demonstration purposes. Maybe he can share his goal?
  - 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