Conditional Formatting did not work with SumProduct and Search array formula

[freelensia]

Hi,

I have a table called BadSyntaxTbl with a column called Bad Syntax.
It looks like this:

Bad Syntax
<!-- b-->< /b>
% {

I want to apply conditional formatting to a range of cells. The formatting would apply when any of the terms in Bad Syntax column are found anywhere in the formatted cell.

So for example, the cells below would trigger the conditional format:
I love the website < b> Freelensia.com < /b>.
Welcome to % {service_name}, a place to reserve interpreters online.

I came up with this formula:
=SUMPRODUCT(--ISNUMBER(SEARCH(BadSyntaxTbl[Bad Syntax],N3)))>0
where N3 is the first cell I want to apply the format to.

I have tested this formula by inputting it in a normal cell. It returned TRUE / FALSE correctly.

However when I put this in a conditional formatting rule, Excel tells me to check my formula again and refuses to process it.

 

[Marcelo Branco]

Structured references don't work in a conditional formatting rule.
Use regular references like $A$2:$A$3

M.

 

[freelensia]

Ah right! How dumb am I! I will solve this with an Indirect formula instead. Thanks!

 

[Marcelo Branco]

Ah right! How dumb am I! I will solve this with an Indirect formula instead. Thanks!

A workaround without Indirect (a volatile function)

Create a named range, say MyRange, using a formula
Name: MyRange
Refers to: =INDEX(BadSyntaxTbl[Bad Syntax],0)

(this range is updated automatically when new rows are added to the table)

Then you can use this formula in CF
=SUMPRODUCT(--ISNUMBER(SEARCH(MyRange,N3)))>0

M.