Finding Data from one cell somewhere on another tab

[superskid]

If I have a number in cell A1 on a tab called "start" and I want to see if that number appears anywhere else on a different tab, Is there a faster way for me to do this than using "OR" for each sell I want it to look at?

 

[NBVC]

This formula will respond with the number of times that number is found in the other tab.

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,Sheet2!A1:Z100)))

adjust tab name and range to suit.

 

[superskid]

How would I use that formatting in conditional formatting. I dont want to count how many times it appers, rather have the cell highlight if it does appear somewhere or remain unhilighted if it doesn't appear.

Thanks again

 

[superskid]

Sorry,

One more thing, the above mentioned forumula seems to count if I am searching for 7172 and it finds 71722. How do I have it count exact matches only?

 

[NBVC]

Activate cell A1,

Activate the CF dialogue thru" Format|Conditional Formatting, select Formula Is from the 1st drop down and then enter

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,Sheet2!A1:Z100)))>0

click Format and choose your formatting.

 

[superskid]

The formatting works great, the problem is the cell highlights if an exact match isnt found. IE 7172 highlights even if it doesnt exists since it found 71722

 

[NBVC]

How about perhaps?

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,Sheet2!A1:Z100)),--(LEN(A1)=LEN(Sheet2!A1:Z100)))>0

 

[superskid]

Seems to be working.

Thanks a ton!

 

[superskid]

How about perhaps?

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,Sheet2!A1:Z100)),--(LEN(A1)=LEN(Sheet2!A1:Z100)))>0

Almost there, but you can't reference another worksheet in conditional formatting. I have tried defining the range Sheet2!A1:Z10 as Data and then referencing Data at both points in the forumula where you mention Sheet2, but I get a #VALUE! Error?

 

[NBVC]

Did you write it as:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,Data)),--(LEN(A1)=LEN(Data)))>0

This works for me.