DATA VALIDATION with INDEX MATCH array, possible?

[KDavidP1987]

Hello all,

Context:
I am working on a spreadsheet for our PMO to automatically enter data from a CSV file into a large table of agent working hours, then calculate the project's budget in a Pivot Table for their reports. They have been very thrilled with the results, however, there is one issue that we came across in design that I haven't been able to solve.

The data entry section moves the information entered over to a calculations table which matches the agent's information up with the team they work for (from a team information table). This is important for separating out team calculations within the pivot table. However, the problem is that a select few agents perform work for multiple teams, depending on what they're needed for, and the PMO needs this to be reflected in the budget.

Question:
I've added a field to the data entry table, which allows the PM to select the team agents work for from a drop-down list, derived from the team table. However, I would like for it to only show the teams the agent is associated within the drop-down list (data validation), so that they can't select an invalid team for the agents, and so that the list will be shorter.

Is it possible, through formula or VBA, to set up a variable array that the data validation list uses, based on the agent's name in column D, so that only the teams they are assigned to will show up in the drop-down list next to their name?

Table5: (Formatted as a table, for data entry and pivot table purposes)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Email[/TD]
[TD]Time Worked[/TD]
[TD]Matched Name[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD]John.Smith@Contoso.com[/TD]
[TD]120[/TD]
[TD]John Smith[/TD]
[TD]RPG[/TD]
[/TR]
[TR]
[TD]John.Smith@Contoso.com[/TD]
[TD]300[/TD]
[TD]John Smith[/TD]
[TD]Java [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

^ So I would like for fields in the column named Team to have a data validation drop-down list in them which reflects the teams (from table 3, below) that John Smith is listed for (RPG and Java). ****

Table3: (formatted as a table, for data entry and pivot table purposes)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Team[/TD]
[TD]Agent[/TD]
[TD]Email[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]RPG[/TD]
[TD]John Smith[/TD]
[TD]John.Smith@Contoso.com[/TD]
[TD]$60.00[/TD]
[/TR]
[TR]
[TD]Java[/TD]
[TD]John Smith[/TD]
[TD]John.Smith@Contoso.com[/TD]
[TD]$60.00[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Jane Doe[/TD]
[TD]Jane.Doe@Contoso.com[/TD]
[TD]$60.00[/TD]
[/TR]
[TR]
[TD]SAP[/TD]
[TD]Jill Smith[/TD]
[TD]Jill.Smith@contoso.com[/TD]
[TD]$60.00[/TD]
[/TR]
</tbody>[/TABLE]

Thank you, in advance!

Sincerely,
Kristopher

 

[Rijnsent]

Hi Kristopher,
that looks like a good challenge. I think this tutorial explains what you're looking for: https://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/
Cheers,
Koen