Conditional Formatting Where the cell content is variable within bounds

Question

I need to create a way of conditional formatting where a manager can enter staff names in a list - used for drop-downs. That must then automatically conditionally format the fields.

What I have currently is a set of 9 background colours assigned to 9 names (9 is the largest number of people under one manager).

This needs to be replicated for every team (a task not worth doing manually)

So my idea is to have something like

+-----------+------------+
| NAMES     | DEFINITION |
+-----------+------------+
| Aaron     | Name 1     |
| Bill      | Name 2     |
| Charlotte | Name 3     |
+-----------+------------+

In the conditional formatting it would be If the current cell contains a name to the left of a Define name, shade it in

If Cell Value = (
Vlookup Cell Value = Cell Value in Sheet "Names", 2 (Name 1, Name 2 etc)

I understand that I can do this in a similar way to:

=VLOOKUP(F2,Sheet2!$H$3:$I$10,2,FALSE)

But I don't know to to turn F2 into "cells chosen to have drop downs in"

So here is the spreadsheet containing names against daily tasks. Each team will have different tasks (rows could be much smaller or much larger) and different employees

My conditional formatting looks like

Rather than Cell Value = "Data" I want something that relates to my drop downs

So each Team Leader can put their employees in their report and won't have to change the conditional formatting

Answer 1

Use the following conditional formatting formula:

=MATCH(E1,NameList,0)=1

Format to suit with fill colour for Employee 1, and apply to $E:$JD.

Repeat for Employees 2-9 - e.g.

=MATCH(E1,NameList,0)=2

=MATCH(E1,NameList,0)=3

etc. up to 9

I've specified a named range NameList for the list of names, as used in your data validation. You can easily replace this range name with an absolute range reference.

Now you can change the names in the list, and the dropdown data validation and conditional formatting will automatically refer to the new list values.

See https://excel.solutions/so54405197_conditionalformatting/ for a worked example file.