Highlight duplicates entries between multiple tabs (5 to 10 different sheets)

[jarrgonn]

I have 6 different sheets for 6 individuals: one sheet per person.

They all take note/track a tracking number that they work on from the same system. Since it's the same system and they process from oldest to newest, there is a possibility that more than one person might touch the same tracking number[s] causing work overlap. Thus, I need the cell to highlight if someone enters a tracking number on their assigned sheet that was/is already worked on.

I need to know what formula I should use for conditional formatting to be able to acheive this. Tried doing countif but it's a bit confusing since it only worked between two tabs on the same excel sheet but not for multiple tabs.

[JohnTopley]

See yellow banner at top of page on how to post a sample workbook.

[jarrgonn]

Please see attached.

So, 3 "taggers" are working on 'Xsystem' where they tag worksheets with unique FAX IDS. To avoid overlapping work, Column A cells should light up when any one of the 3 taggers type in a FAX ID that someone else has already typed on their respective worksheet.


The FAX IDS are always different per day and is never the same even from previous (or future) days. Also, "3 taggers" is just a reference; I actually have 15 people for that area of my department. They'll all be working on this shared worksheet.

[DJunqueira]

My proposed solution involves Power Query.
PQ will join all numbers in a single column and then will generate a single list or numbers that are repeated.

To be placed in the conditional formatting.

Please Login or Register  to view this content.

[JohnTopley]

A possible solution is to use VBA Worksheet Change to validate entries [and issue error message] as they are typed in - either against entries in each sheet or as per previous pos,t a list of all entries to date.