Find Dupes across multiple wkshts and color the cell

[NGBFLNG1]

Hi All,

I am a newbie and my first post--hoping to get some help.

I am tracking appointments and training qualifications by name (of person appointed) and date of qualification. Each type of appointment has its own worksheet, but each appointment may have some of the same training qualifications.

What I want is to highlight (color) the cell that contains the person's name if it is duplicated on another worksheet. I do not know how to do this across multiple worksheets.

I have attached the file for your convenience. I would appreciate any help. :)

[Pauleyb]

Use conditional formatting. For example, in the CaGs sheet, highlight B1:B39 and choose conditional formatting. Choose 'UseFormula...' and use this formula:
=NOT(AND(ISERROR(MATCH(B1,Fiscal!$B$2:$B$66,0)),ISERROR(MATCH(B1,DTS!$B$2:$B$6,0))))
and set the formatting how you wish.

Note that the formula above does not include the empty P&C sheet, but you can add it with another ISERROR(....) block.

[Vikas_Gautam]

Done it..
Here is your conditional formatting formula.. ( CF will treat it as an array...)

Please Login or Register  to view this content.

Check the attached file..

[NGBFLNG1]

Hi Pauleyb,

I tried your suggestion, but it gave me an error: "You cannot use conditional formatting to reference other worksheets"..which is the problem. I need all instances of the dupe names to be highlighted in a color so I can readily see them.

[Pauleyb]

Hmm, that's strange it worked for me. I have 2010, but I don't think that changed. Attached is your file, modified with my suggestion.

{Added 5 minutes later}
Apparently, the ability to go across sheets is a new feature in 2010.
http://blogs.office.com/2009/08/11/m...in-excel-2010/

However, in 2007 you can use named ranges to get around that shortcoming.

So, the equation would be something like:
=NOT(AND(ISERROR(MATCH(B1,FiscalData,0)),ISERROR(MATCH(B1,DTSData,0))))

It also looks like Vikas provided another solution, but I know INDIRECT tends to slow things down. Probably not a problem if your dataset is relatively small.

[NGBFLNG1]

That works, but only if there are dupes on the same page. I need it to highlight the dupe across all pages at once.

If, for example--Doe, Jane were duplicated on 'CaGs' and then was also added to the 'Fiscal' worksheet, she would be highlighted on both spreadsheets at once.

[NGBFLNG1]

Hi Vikas,

I downloaded your file that you sent back and I see that it did highlight the dupe names from the other worksheets, which is what I wanted it to do--but, is there a way to modify it so the dupe names from the other spreadsheets are highlighted at the same time.

Example: If Wiseman, William J. [cell B44] is highlighted on the 'CaGs' WS b/c he is also on the 'Fiscal' WS [cell B6], I would like that cell to be highlighted as well. Thank you.

[NGBFLNG1]

Hi Vikas,

How do u get it to treat like an array? [Ctrl] [Alt] [Enter]? Do I enter the formulat into the Conditional formatting section first? I have never used arrays and was just reading about them on Excel 'help'.

Also, what is the significance of the following in your fomula?:

P$2:$P$5 and >1

[Pauleyb]

For both approaches, you will need to set up CF for each range on each sheet. Just modify the provided formula to include the other sheets (and remove the current sheet). So, with four sheets, you will need four CF.

I'll also answer your last question. Don't worry about it. CF automatically interprets the formula as an array.

[Vikas_Gautam]

Here is the revised workbook...
All dupes have been highlighted on each worksheet....
NOTE:-
There in not need of pressing CTRL + SHIFT + ENTER, because CF will automatically treat it like array formula..

Check the attached file..

[NGBFLNG1]

I see it works in the spreadsheet you uploaded to me, but I wonder why I cannot duplicate your formula in my own copy of the workbook. I copy and pasted your formula into the Conditional Formatting (CF) section. Is there something I'm missing?


Oh I think I am missing something in cells P2 through P4. Let me check that out first and then I will write "Solved" to this thread. Thx.

[Vikas_Gautam]

After putting sheet names in $P$2:$P$5
Steps..
1. First Select the Column B wholly
2. Then apply the new conditional formatting formula ie. =SUM(COUNTIF(INDIRECT("'"&$P$2:$P$5&"'!$B:$B"),B2))>1
3. Choose your appropriate color and press ok..

thats it..

Regards,