TL:DR - I want to highlight duplicates names across 50+ small lists in the same sheet.

Good day, I am struggling with what I thought would be a simple use case for conditional formatting but turns out to be too complex for my 'middle-of-the-road' excel skills! Any help would be so much appreciated.

- I have a spreadsheet which is used to manage an organisation made up of multiple groups of teams.
- The team members are all stored on a separate tab which feed the drop down list so that the user can only select a named person from the master list.

NEED:
I want the sheet to automatically highlight duplicates, so that it is obvious where a person has been named multiple times in different teams as different people are responsible for their own teams within the sheet.

REQUIREMENTS:
For now, I want to highlight yellow if a person is named twice anywhere in the sheet, and highlight red if a person is named 3 or more times anywhere in the sheet.

Screenshot 2021-03-20 at 13.15.52.png

CURRENT WORKING FORMULAS
I have been able to get this working within a single team, using some Google fu… but this is not of any use as the point of this rule is for it to apply across multiple teams (there are around 20 groups of 2-3 teams in the sheet).

I currently have these for Column D (I have to be honest, I don’t really understand how this even works, but it does):

Highlight Yellow =COUNTIF($D$15:$D$29,D15)=2
Highlight Red =COUNTIF($D$15:$D$29,D15)>2

And then a replicated rule for Column J:
Highlight Yellow =COUNTIF($J$15:$J$29,J15)=2
Highlight Red =COUNTIF($J$15:$J$29,J15)>2

etc.

Screenshot 2021-03-20 at 13.28.54.png

PROBLEM
I am not able to get this working to find duplicates across multiple teams, even for the 3 teams in the screenshot, let alone across the others off screen. I am looking to be able to highlight duplicates across all teams and ideally in the simplest way possible.

Screenshot 2021-03-20 at 13.20.58.png

I have attached the workbook for reference. Thanks in advance!