Highlighting duplicates on different sheets

Trying to find duplicates in columns on two different spreadsheets, and highlight them as yellow.

One file is "LeadShare" and the reference area is F2:F27

The other is "Install Customer List Pac NW- Diaz" and the reference area is G4:G745

I want the values on the Install Customer list to show as yellow fill if they are already stated on the first (Leadshare) list. Any help is greatly appreciated!

Originally Posted by lalexis

Trying to find duplicates in columns on two different spreadsheets, and highlight them as yellow.

One file is "LeadShare" and the reference area is F2:F27

The other is "Install Customer List Pac NW- Diaz" and the reference area is G4:G745

I want the values on the Install Customer list to show as yellow fill if they are already stated on the first (Leadshare) list. Any help is greatly appreciated!

Conditional formatting between workbooks is possible but not instantly.

If you're open to the idea of a "helper column" you could use COUNTIF to check which values appear on both workbooks then base the conditional formatting around that.

Let me know if you need more information on it.

S.

> Trying to find duplicates in columns on two different spreadsheets, and
> highlight them as yellow.
>
> One file is "LeadShare" and the reference area is F2:F27
>
> The other is "Install Customer List Pac NW- Diaz" and the reference area
> is G4:G745
>
> I want the values on the Install Customer list to show as yellow fill if
> they are already stated on the first (Leadshare) list.

Here's one way with Excel 2003 without using a helper column.

First define a name "LS":
Insert > Name > Define
Then in the input field just below "Names in workbook" put this name:
LS
and for "Refers to" put
=LeadShare!$F$2:$F$27
and click "Add"

Then select G4 in the customer list and use
Format > Conditional Formatting > Formula Is >
=COUNTIF(LS,G4)>0
and choose the "Format" desired.

Then copy the cell format of G4 to the rest of the column.

Hope this helps.