I need a formula in contitional formatting

[tedmed]

I don't know how to say, so look at the image:

https://www.dropbox.com/s/rmejl7f0xif7cmb/excel.png?dl=0

Read the text on image. If skills are selected below with any character (for image i used "x" below "passing"), then cells in table below with the same text have yellow background. So i would use conditional formatting but don't know right formula for only one rule. Is it possible to have only one rule? I can use 25 rules, this mean, one rule for every cell where i can enter anything to select skill.

Thanks in advance.

 

[Toadstool]

If you want one rule then select from D19 to H25 (or as far down as your data goes) and for the Conditional Formatting use the Rule Description:

=OR(IF(IFERROR(PROPER(INDEX($B$5:$P$5,MATCH(D19,$B$4:$P$4,0))),FALSE)="X",TRUE,FALSE),IF(IFERROR(PROPER(INDEX($B$9:$P$9,MATCH(D19,$B$8:$P$8,0))),FALSE)="X",TRUE,FALSE),IF(IFERROR(PROPER(INDEX($B$13:$G$13,MATCH(D19,$B$12:$G$12,0))),FALSE)="X",TRUE,FALSE))

 

[tedmed]

Thanks, it works. Very long formula containing some functions i don't have a clue about
You used ="x". If i don't use x, cells in table won't be yellow. So i'd like either to be able to use any character to select a skill (i replaced "=x" with "<>"" " but this made all cells yellow) or to automatically change any entered character which isn't "x" to "x".

 

[Toadstool]

Hi tedmed,

I've changed it to check the length of whatever you type in as an indicator. If it's one or more characters then it does the check.

Try this:

=OR(IFERROR(IF(LEN(INDEX($B$5:$P$5,MATCH(D19,$B$4:$P$4,0)))>0,TRUE,FALSE),FALSE), IFERROR(IF(LEN(INDEX($B$9:$P$9,MATCH(D19,$B$8:$P$8,0)))>0,TRUE,FALSE),FALSE), IFERROR(IF(LEN(INDEX($B$13:$G$13,MATCH(D19,$B$12:$G$12,0)))>0,TRUE,FALSE),FALSE))

You've non-contiguous cells to check so putting it in one formula is tricky. I'm using three formulae, one for each set of rows (4/5, 8/9 and 12/13) wrapped in an OR statement so if any one is TRUE then it highlights the cell.

All addresses are absolute except D19 as that's the top/left cell so when Conditional Formatting evaluates it also looks in D20, D21, etc and E19, E20, E21 etc.

Taking the first of the three formulae (those looking at rows 4 and 5) then:

• MATCH(D19,$B$4:$P$4,0) is looking for the skill in D19 ("speed") across B4 to P4 and returning the column number.
• INDEX($B$5:$P$5, uses the column number from MATCH to retrieve the cell from the row below, row 5.
• IF(LEN checks the length of whatever the INDEX returned for >0 and if so returns a TRUE, otherwise it returns a FALSE.
• IFERROR is there in case no match is found of D19 in B4 to P4 in which case the MATCH returns an N/A so IFERROR catches that and sets the result to FALSE.

The same is the done in the OR for the next two sets of rows to check which gives you the formula above.

Does that make sense?

 

[tedmed]

So i have to replace ="x" with >0 ?
Thanks.

How about this: how can be cell's value either nothing or x, i mean automatically changed something else to x? The closest i can do is validation with formula =COUNTIF(B5,"x") so i get error message if i don't enter x.

 

[Toadstool]

"So i have to replace ="x" with >0 ?"
No, the formula in post #4 will highlight the cell if any character is put under the matching word.

I did this because you asked "i'd like either to be able to use any character to select a skill"

 

[tedmed]

Now something else. Look at the image again. I'd like to use conditional formatting for cells in Drill column (starting at A19) so that e. g. cell A19 is yellow too if any of cells in range D19:H19 are is yellow (true in your formula in conditional formatted): I used this **** long formula:

=OR(OR(IF(IFERROR(PROPER(INDEX($B$5:$P$5;MATCH(D19;$B$4:$P$4;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$9:$P$9;MATCH(D19;$B$8:$P$8;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$13:$G$13;MATCH(D19;$B$12:$G$12;0)));FALSE)="X";TRUE;FALSE));OR(IF(IFERROR(PROPER(INDEX($B$5:$P$5;MATCH(E19;$B$4:$P$4;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$9:$P$9;MATCH(E19;$B$8:$P$8;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$13:$G$13;MATCH(E19;$B$12:$G$12;0)));FALSE)="X";TRUE;FALSE));OR(IF(IFERROR(PROPER(INDEX($B$5:$P$5;MATCH(F19;$B$4:$P$4;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$9:$P$9;MATCH(F19;$B$8:$P$8;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$13:$G$13;MATCH(F19;$B$12:$G$12;0)));FALSE)="X";TRUE;FALSE));OR(IF(IFERROR(PROPER(INDEX($B$5:$P$5;MATCH(G19;$B$4:$P$4;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$9:$P$9;MATCH(G19;$B$8:$P$8;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$13:$G$13;MATCH(G19;$B$12:$G$12;0)));FALSE)="X";TRUE;FALSE));OR(IF(IFERROR(PROPER(INDEX($B$5:$P$5;MATCH(H19;$B$4:$P$4;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$9:$P$9;MATCH(H19;$B$8:$P$8;0)));FALSE)="X";TRUE;FALSE);IF(IFERROR(PROPER(INDEX($B$13:$G$13;MATCH(H19;$B$12:$G$12;0)));FALSE)="X";TRUE;FALSE)))

This mean OR(your formula for D19, for E19, for F19, for H19)
Maybe this can be shorter with using sumproduct, i don't know.