Conditional formatting triggered by multiple text options in a different cell I\

Question

Conditional formatting triggered by multiple text options in a different cell

I'd appreciate any help I can get.

I have a worksheet that people will fill out with different codes.

I want a cell (F5) to appear to be blank (font color is white) unless another cell (or groups of cells) contains a certain code.

If they do, then the font in F5 will be black.

The problem I have is making a rule that will cover the different variations of the "trigger" code, which could be three different codes, in either uppercase or lowercase.

Is there a way to assign multiple conditions to a cell???

Right now, my formula is (F5-font is black) =$K$16="33elect0"

is there a way to make it (F5-font is black) if K16 contains "elect" (regardless of case)?

Or (F5-font is black) if K16 contains "elect" OR "util" OR "prop" (also regardless of upper or lower case?

Or will I have to enter the exact code and iits variant?

Rule 1 (F5-font is black) =$K$16="33elect0"

Rule 2 (F5-font is black) =$K$16="33ELECT0"

Rule 3 (F5-font is black) =$K$16="33util0"

Rule 4 (F5-font is black) =$K$16="33UTIL0"

and so on...

And I hope to copy thiss rule for cells K19, K22, K25 etc.

I hope this makes some kind of sensee.

Explanation / Answer

Untested rambling:

First, consider using UPPER on the cell value to reduce the number of formulas.

Next, use FIND to see if your substring ("elect", "util", etc) is present. If not it will return an error, so use

ISERROR to check.

ISERROR will actually give you the opposite of what you want, a value if it is an error, and FALSE (zero) if it isn't an error, so I think (again, untested) you could do something like:

=(ISERROR(find("ELECT",UPPER(K16)))*1)*(ISERROR(find("UTIL",UPPER(K16)))*1)*(etc)

If my logic is right, then if any of those strings are found, one of the values will be zero, so the whole string will be zero. So in your conditional formatting dialogue (use formula)

=0=(Iserror....

Even if that doesn't work exactly, hopefully it gets you close enough to figure it out. If it doesn't work, try each piece of the formula individually so you can see the results in a cell, then keep putting the smaller pieces together until you have a cell formula that returns a zero or onne, then stick that in your conditioonal formatting formula boox

You could also use an array formula to check each of your keywords and only have one formula, but I suuggest understanding how the non-array version works first- otherwise you'd have a harder time troubleshooting the array version

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.