Data Validation based on multi-table matches|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I am creating a workbook with six tables, as follows:

TABLE #1: tblARTS

Purpose: Hold the names of SAFe Agile Release Trains

• [ART ID], [ART NAME], [ART STATUS]
• [101], [East], [Active]
• [102], [West], [Active]
• [103], [North], [Inactive] <-- notice this ART is inactive

TABLE #2: tblTEAMS (Agile Teams)

Purpose: Hold the names of agile teams within each ART 

• [TEAM ID], [ART NAME], [TEAM NAME], [TEAM STATUS]
• [201], [East], [Angels], [Active] <-- notice this team name is not unique w/o ART name
• [202], [East], [Bears], [Active]
• [203], [East], [Cubs], [Active]
• [204], [West], [Giants], [Active]
• [205], [West], [Patriots], [Inactive] <-- notice this team is inactive
• [206], [West], [Cowboys], [Active]
• [207], [West], [Angels], [Active] <-- notice this team name is not unique w/o ART name 

TABLE #3: tblINCREMENTS (Valid values for SAFe Program Increments)

Purpose: A list of valid PI numbers managed by the admin on a locked worksheet

• [PI ID], [PI NAME]
• [301], [PI-1]
• [302], [PI-2]
• [303], [PI-3]

TABLE #4: tblSPRINTS (Valid values for SAFe Iterations)

Purpose: A list of valid sprint numbers managed by the admin on a locked worksheet

• [SPRINT ID], [SPRINT NAME]
• [401], [S-1]
• [402], [S-2]
• [403], [S-3]
• [404], [S-4]
• [405], [S-5]
• [406], [S-6]
• [407], [S-7]
• [408], [S-8]
• [409], [S-9]
• [410], [S-10]
• [411], [S-11]
• [412], [S-12]

TABLE #5: tblTIMEBOXES (SAFe PI Sprint Combo)

Purpose: Create a list of unique combinations of ART, PI, and sprint

• [TIMEBOX ID], [ART NAME], [PI NUMBER], [SPRINT NUMBER]
• [501], [East], [PI-1], [S-1]
• [502], [East], [PI-1], [S-2]
• [503], [East], [PI-1], [S-3]
• [504], [East], [PI-1], [S-4]
• [505], [West], [PI-1], [S-1]
• [506], [West], [PI-2], [S-2]
• [507], [West], [PI-2], [S-3]
• [508], [West], [PI-2], [S-4]
• [508], [West], [PI-2], [S-5]

TABLE #6: tblVELOCITY (Sprint Velocity Data)

Purpose: Record team performance in each sprint (where each team is unique to an ART, and each sprint is unique to an ART+PI)

• [VELOCITY ID], [ART NAME], [TEAM NAME], [PI NUMBER], [SPRINT NUMBER], [VELOCITY]
• [601], [East], [Angels], [PI-1], [S-1], [25]
• [602], [East], [Bears], [PI-1], [S-1], [40]
• [603], [East], [Cubs], [PI-1], [S-1], [35]
• [604], [West], [Giants], [PI-1], [S-1], [82]
• [605], [West], [Cowboys], [PI-1], [S-1], [60]
• [606], [West], [Angels], [PI-1], [S-1], [110]
• [607], [East], [Angels], [PI-1], [S-2], [22]
• [608], [East], [Bears], [PI-1], [S-2], [42]
• [609], [East], [Cubs], [PI-1], [S-2], [31]
• [610], [West], [Giants], [PI-1], [S-2], [78]
• [611], [West], [Cowboys], [PI-1], [S-2], [62]
• [612], [West], [Angels], [PI-1], [S-2], [104]

I need help with data validation to ensure that only valid data combinations are entered or selected, as follows:

DATA VALIDATION: tblARTS

• [ART NAME] is mandatory
• [ART NAME] cannot include the pipe "|" character
• [ART NAME] must be unique
• [ART STATUS] is mandatory

DATA VALIDATION: tblTEAMS

• [ART NAME] should be a dropdown list of only the "Active" ART names from the tblARTS table
• [TEAM NAME] is mandatory
• [TEAM NAME] cannot include the pipe "|" character
• The combination of [ART NAME] and [TEAM NAME] must be unique
• [TEAM STATUS] is mandatory

DATA VALIDATION: tblINCREMENTS: N/A (table is on hidden sheet)

DATA VALIDATION: tblSPRINTS: N/A (table is on hidden sheet)

DATA VALIDATION: tblTIMEBOXES

• [ART NAME] should be a dropdown list of only the "Active" ART names from the tblARTS table
• [ART NAME] is mandatory
• [TEAM NAME] should be a dropdown list of only the "Active" team names from the tblTEAMS table - filtered to include only those teams that belong to the selected ART
• [TEAM NAME] is mandatory
• [PI NUMBER] should be a dropdown list of increments from the tblINCREMENTS table 
• [PI NUMBER] is mandatory 
• [SPRINT NUMBER] should be a dropdown list of increments from the tblSPRINTS table 
• [SPRINT NUMBER] is mandatory 
• The combination of [ART NAME] + [TEAM NAME] + [PI NUMBER] + [SPRINT NUMBER] must be unique

DATA VALIDATION: tblVELOCITY

• [ART NAME] should be a dropdown list of the ART names in the tblTIMEBOXES table (not the tblARTS table)
• [ART NAME] is mandatory
• [TEAM NAME] should be a dropdown list of the teams in the tblTIMEBOXES table (not the tblTEAMS table), but must be filtered to include only those teams that belong to the ART selected in the previous step
• [TEAM NAME] is mandatory
• [PI NUMBER] should be a dropdown list of the PIs in the tblTIMEBOXES table (not the tblINCREMENTS table), but must be filtered to include only those PI numbers where both the selected team and selected ART are a match
• [PI NUMBER] is mandatory 
• [SPRINT NUMBER] should be a dropdown list of the sprints in the tblTIMEBOXES table (not the tblSPRINTS table), but must be filtered to include only those sprint numbers where the selected team, ART, and PI are a match
• [SPRINT NUMBER] is mandatory
• The combination of [ART NAME] + [TEAM NAME] + [PI NUMBER], [SPRINT NUMBER] must be unique

CONDITIONAL FORMATTING

I'd like to turn any row in any of these tables to a red background with white text if any of the relevant rules above have somehow been violated.

Ultimately, the goal here is to prevent velocity data from being entered for invalid ARTs, team, PIs, and sprints.

I am hoping to do this with only formulas, but I can go the VBA route if required.

Many thanks in advance... and happy holidays!