Conditional fomatting based on AND(), INDEX() and MATCH() function?

[knjf85]

I have two spreadsheets with named tables.

The first spreadsheet is named Components, and the table name is also Components
Some of the headers are Art.no and PAH


In my second spreadsheet I have another table linking some of the infomation from those components.

Here I want to do some conditional formatting in column P.

The formula I'm trying to use (and combinations of it) is:

=AND(INDEX(Components[PAH];MATCH([@[Art.no]];Components[Art.no];0))="Yes";P1="")

Basically I want to see if the PAH-column in components is yes AND P1,P2,P3,... is empty.
It matches the spreadsheets Art.no-column with the same column in the Components-spreadsheet.

But it doesn't work, it always gives me the error-message:
The formula you typed in contains an error.

I tried to foolproof the formula with a standard IF()-formula in an empty cell and then it works with a correct result

=IF(AND(INDEX(Components[PAH];MATCH([@[Art.no]];Components[Art.no];0));="Yes";P1="");"true";"false")

What is wrong with the same formula using Conditional formatting?

 

[oldbrewer]

let us say your test is if A1 = 999

in B1 =if(A1=999,"YES", "NO")

so far so good

if you want to colour C1 if A1=999 the CF formula is =A1=999

you are using a generic if A1 =999,true,false but you do not want to colour the cell in EITHER case

so you need only =A1=999 or you need to put a "yes" in a helper cell - say G1 then in A1 put CF =G1="yes"