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

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

What is wrong with the same formula using Conditional formatting?

Hi

Enclosed is an example worksheet.

I have copypasted many columns without doing any proper conditional formatting.
Please only focus on the P-column in the spreadheet Component-per-supplier and D-column (PAH) in Components-spreadsheet.

My first plan was to have the requirement (yes/no) in the O-column in Component-per-supplier.
This is why I have a format on P4 with the rule =AND(O1="Ja";P1="")

But as I can have the same item several times (row 4 and 10 in the example file), I want to look at the component-specific requirement from the Component-sheet instead.

This is why I would like to change the format rule to
=AND(INDEX(Components[PAH];MATCH([@[Art.no]];Components[Art.no];0))="Ja";P1="")

Hope this explains the problem

Hi FDibbins,

Well, I didn't use the IF-formula to do formatting, just had it for foolproofing.

Thank you, it seems to work (after changing commas to semicolons).

But the problem with that formula is that it is locked to row 8 in the Components-sheet.

If I insert a new row in the middle it adjusts the coditional formatting to row 9.

But if I go to the last row, enter new information so it becomes part of the table, then conditional formatting-formula doesn't recognize the new row.

This is why I would prefer to use Components[Art.no] instead of Components!$A$3:$A$8

If it's not possible to refer to tables in conditional formatting, maybe I need to refer to row 5000 or something like that, but I'd like to avoid that..