Conditional formating maybe? (Stuck)

[CyannideHigh]

Hey there guys (And Girls),

I have a problem i just can't solve, ok here it is i have 2 sheets within my document that will have a VERY similar list in them. i would really like to see if i am putting in duplicates in either list without having to jump back and forth.

I was personally thinking conditional formatting but i don't know how to get both columns selected without excel "arguing" with me about it XD

Any ideas?

Thanks in advance

 

[James006]

Hello again,

In your Sheet Evo ... in cell L2 ...could you test the following formula :

=IFERROR(AND(ISNUMBER(MATCH(H2,Master,0)),INDIRECT("Gear!B"&MATCH(H2,Gear!C:C,0))="Y"),FALSE)

And could you confirm the results produced TRUE - FALSE are all correct ...

 

[CyannideHigh]

=IFERROR(AND(ISNUMBER(MATCH(H2,Master,0)),INDIRECT("Gear!B"&MATCH(H2,Gear!C:C,0))="Y"),FALSE)

This produced a "FALSE"

 

[Fluff]

Select H2 to H200 & use this
=AND(INDEX(Gear!$B$2:$B$200,MATCH($H2,Gear!$C$2:$C$200,0))="Y",ISNUMBER(MATCH($H2,Gear!$C$2:$C$200,0)))

When using CF it's best to avoid using whole column references.

 

[CyannideHigh]

Thanks guy i think you've cracked it seems to be working pretty well now

 

[Fluff]

You're welcome & thanks for the feedback