Conditional formatting with formula(s) across multiple ranges

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!

Select range D15:AH29
Formula for Conditional Formatting, new red rule:

Formula:

=AND(D$14="Name";COUNTIF($D$15:$AH$29,D15)>=3)

Adjust it for yellow rule.

Make sure the box "Applies to" is the whole range $D$15:$AH$29

Good luck!

Thanks, Estevaoba. The conditional formatting formula box returned an error, so I assumed that should read

"Name",

and it worked fine across the range from row 15 to 29 in my dummy sheet.

However, when I try to apply the Red rule to the full range in the sheet (so $D$15:$AH$134) it messes things up and highlights some incorrect cells (cells that don't contain names). The Yellow rule seems to work fine.

Attachment 724768

I have attached an updated version of the workbook with the updated formatting rules/formulas applied.

The reason it messed things up is that the formula relies on a row with the text "Name".
So I'm afraid you can't have one rule covering all the teams. You'll have to do this in sections, one rule for each set of teams.
The rule as specified above for the first set of teams, rows 15 thru 29, the formula below for next set of teams, starting at row 59, and so on.

Formula:

=AND(D$58="Name";COUNTIF($D$59:$AH$73,D59)>=3)

For this rule, the box "Applies to" will be $D$59:$AH$73

Happy trails!