Data Validation Droplist based on expanding named range |General Excel Questions & Answers|Excel Forum|My Online Training Hub

I have a one-column table called tblOffices (named range is rngOffices). The table allows any text value and currently has three rows (plus header):

[Office]

[1] [New York]

[2] [Miami]

[3] [Chicago]

Within each office there are teams. Teams are managed in the tblTeams table (named range is rngTeams). The first column uses a Data Validation list (=rngOffices) to limit selection to an office from rngOffices. The second column is free-text for the team name. 

[Office][Team]

[1] [New York] | [NY-Team-1]

[2] [Miami] | [Miami-Team-1]

[3] [New York] | [NY-Team-2]

[4] [New York] | [NY-Team-3]

[5] [Miami] | [Miami-Team-1]

[6] [Chicago] | [Chicago-Team-1]

Finally, there is a third table (tblNicknames] where each team is assigned a free-text nickname. There are only three columns:

[Office][Team][Nickname]

[1] [New York] | [NY-Team-1] | [Yankees]

[2] [Miami] | [Miami-Team-1] | [Dolphins]

[3] [New York] | [NY-Team-2] | [Mets]

[4] [New York] | [NY-Team-3] | [Giants]

[5] [Miami] | [Miami-Team-2] | [Heat]

[6] [Chicago] | [Chicago-Team-1] | [Bulls]

In the tblNicknames table, the values for [Office] and [Team] are chosen via use Data Validation droplists ("=rngOffices" and "=rngTeams").

Here's where my challenge is. Right now, a user can select a combination of Office and Team that doesn't make sense (e.g., [New York] | [Heat]) and assign that non-existent team a nickname.

When a user selects an Office (e.g., New York), I want to restrict the values in the Data Validation for Team to only the teams in that office.   

Please note that the list of office locations and teams are both dynamic. For example, we will soon add Cleveland and Kansas, both of which will have teams (and each team will get a nickname). 

Thanks