Dynamic named range

I created a table in Excel 2010 with the name of several teams (in column B) and the names of the employees (in column C).
I would like to create named ranges of the employees where the name of the team is identical.
E.g.
Column B Column C
VVM John
VVM Roy
VVM Linda
TZ Jack
TZ Carol
etcetera.
The first named range's name should be Team_VVM (referring to John, Roy and Linda).
The second named range's name should be Team_TZ (referring to Jack and Carol).
If Teammembers are added, the named range names should dynamically change.

Thanks for the help.

Hi rgsbouma,

The attached should cover what you're looking to achieve.

Regards,

Snook

Thanks, that works brilliantly.
The next challenge is to use this dynamic range in a data validation.
When I select the team in column B (e.g. VVM), a Vlookup searches the table (lookup_Team) below:
Column A Column B
VVM ddl_Team_VVM
TZ ddl_Team_TZ

However, the =INDIRECT(VLOOKUP($A2,lookup_Team,2,false)) function as source if the data validation results in am error.
I do not understand why.

Please advise.

Hi rgsbouma,

Unfortunately it would appear (I never knew this) that Excel has an issue with using the INDIRECT function in data validation's with a dynamic range.
I was having the same problem as you and googled it and there are multiple sites highlighting the same issue.

I tried to find a solution but couldn't get any of them to work. This seemed like the most promising workaround (Link).

Could anyone else assist rgsbouma?

Regards,

Snook