Conditional formatting for text not in a table column

[gberg]

I am trying to come up with a Conditional Formatting that highlights text that does not equal the one of the options from a table column.

The Conditional Formatting applies to $B:$36:$B$45 and I have this formula for the conditional formatting

=AND(ISNA(MATCH(B36,INDIRECT("TBL_Labor_Categories[CODE]"))),B36<>0)

It highlights issues in cell B36 but not in B37-B45

I would appreciate any help

 

[Kaper]

Would use 0 as third parameter of MATCH help? (exact match, table can be not sorted)

=AND(ISNA(MATCH(B36,INDIRECT("TBL_Labor_Categories[CODE]");0)),B36<>0)

 

[gberg]

I get an error message when I put that in

 

[Kaper]

Whoops, you shall of course use comma as separator. In my local settingd I use semicolon, so this is what I wrote.

But it is:

=AND(ISNA(MATCH(B36,INDIRECT("TBL_Labor_Categories[CODE]"),0)),B36<>0)

 

[gberg]

That did it! Thanks for your help!

 

[Kaper]

By the way, INDIRECT is not only way as workaround to refer to table column in conditional formatting.
Probably better way (I think) would be by creating a name, and referring to that name in CF rule,
so the rule would be something like:

=AND(ISNA(MATCH(B36,TBL_Labor_Categories_CODE,0)),B36<>0)

where TBL_Labor_Categories_CODE is a name for TBL_Labor_Categories[CODE]

In names referring to structured address (like column in table) is perfectly fine.

See the screenshot

 

[gberg]

Thanks for the additional advice. I've heard that INDIRECT is a volatile function. Would your suggestion make the spreadsheet work better (faster)?

 

[gberg]

I had a couple of other items with the INDIRECT and used your advice on those as well. Thanks again!

 

[Kaper]

Indeed, volatile functions can make workbook react slower, as they are recalculated always when the workbook recalculates (even if their arguments do not change).
If you have few of them, you will not notice this performance decrease, but when they are used often in a large workbook - yes, you will see the influence.
Using non-volatile approach is (I think) quite commonly perceived as "elegant way" to build a workbook.

But the idea of limiting volatile functions usage is just a suggestion, not a strict rule.

Have a good day!