Conditionial formatting

[brems]

Hi,

I have a file with some hard-coded conditional formatting. I attached an example.

The conditions for formatting the cells are hard-coded in the conditional formatting itself but I want to have the possibility to have the conditions refer to the cells C2, C3 and C4.
I know this is possible when I remove the operators from the specific cells but I wonder if a relative conditional formatting is possible with the operators (<>=) still in the specific cell.

If for example cell C2 is changed to <90% the conditional format is applied to the range A1:A20.

Thank you for having a look at my question.

 

[chirayu]

As far as I know, nope can't have logical operators within a cell as criteria for conditional formats. The symbols must be in the formulas but values can be cell references. Example attached.

 

[chirayu]

I thought of a workaround to be able to use logical operators but still need to split the orange cell

 

[Khalid NGO]

Hi brems,

Check these (using chirayu's approach)

for C2 (> = 90%)
=COUNTIF(A1,SUBSTITUTE(C$2," ",""))

for C3 (> = 80% en < 90%)
=AND(COUNTIF(A1,SUBSTITUTE(LEFT(SUBSTITUTE(C$3," ",""),FIND("%",SUBSTITUTE(C$3," ","")))," ","")),COUNTIF(A1,MID(SUBSTITUTE(C$3," ",""),FIND("<",SUBSTITUTE(C$3," ","")),LEN(C$3))))

for C4 (< 80%)
=COUNTIF(A1,SUBSTITUTE(C$4," ",""))

Regards,

PS: @chirayu, IF not necessary

 

[Badale Ankit]

I have done this using helper columns. See if this logic serves your purpose.

 

[brems]

Hi chirayu, Khalid and Badale,

Thank you very much for the offered solutions. They sure meet my requirement and the offered solutions are very inspirational on approaching and solving Excel challenges!

Thanks again.
Brems