Conditional formating if value is not found in range and a value present in Column A (is there a better way)

[MRDBS]

Hi

I am using this condition, but it makes my workbook sluggish:

=AND($A1>1;$A1<>'Sheet2'!$B:$B)

I want to highlight cells which do only meet these criteria.
A1 has a value
But A1 not found in Sheet2 column B

let me hear it!

 

[etaf]

Maybe
can you select a range for B:B rather than the whole column
also
=AND($A1>1; countif( 'Sheet2'!$B1:$B1000, $A1 ) =0)

change the rows 1000 to however many rows you are likely to have in the sheet2

 

[MRDBS]

Maybe
can you select a range for B:B rather than the whole column
also
=AND($A1>1; countif( 'Sheet2'!$B1:$B1000, $A1 ) =0)

change the rows 1000 to however many rows you are likely to have in the sheet2

Worked! Thanks
I also cleaned up some redundant conditions in the process.

 

[MRDBS]

Maybe
can you select a range for B:B rather than the whole column
also
=AND($A1>1; countif( 'Sheet2'!$B1:$B1000, $A1 ) =0)

change the rows 1000 to however many rows you are likely to have in the sheet2

Just one question though I see you changed it to:

=AND($A1>1; countif( 'Sheet2'!$B1:$B1000, $A1 ) =0)

Is that more streamline than: $A1<>'Sheet2'!$B:$B)

 

[etaf]

you are welcome,

I'm not sure , to be honest , and not sure that format works , hence why i choose countif()
BUT with B:B is address , in the latest version of excel , that is checking over a Million cells in an array , which it has to hold in memory
I'm sure other members with more detailed knowledge will be able to explain, or even offer better , faster, solutions

 

[Joe4]

I always use COUNTIF for things like this too. I would NEVER recommend using that Array solution, especially checking every single cell in column B. That is very inefficient and slow, as you have seen.

In any these formulas, the more you can limit the number of rows you check the better.
Just pick the smallest row number that you know you will never exceed.

 

[MRDBS]

Ahh of course, makes sense now! I was confused since I tried countif, but did not get it to work because I was missing the =0 at the end.

Cool thing about excel is, you always learn something new.