Conditional formating across different sheets w custom formula

0
votes

I want to build a custom formula for formatting the background color of each cell of sheet MyList.

The tables in image above are in two separated sheets in the same spreadsheet. I want to format the sheet MyList in red if the value is not find in the sheet MyKart.

I tried the following formula, but it does not work:

IFERROR(if(MATCH(A1,MyKart!A:A,false)>0,true,false),false)=TRUE

The image below illustrates the result that I expect w the conditional formatting:

enter image description here

2
google-sheetsconditional-formatting

2 Answers

2
votes

In conditional formatting, when referring to a different sheet INDIRECT must be used.

The formula should be

=IFERROR(if(MATCH(A1,INDIRECT("MyKart!A1:A"),false)>0,true,false),false)=TRUE

OR as mentioned by @basic

=COUNTIF(INDIRECT("MyKart!A1:A"),A1)
1
votes

Use formula in CF:

=COUNTIF(MyKart!A:A,A1)

enter image description here