Data Validation formula?

[ExcelFatigue]

Hi just needing some advice

i have a dashboard but looking to make it easier to select agents from each team they have from the list

i had inital a list of all agents from data validation however this made it harder for management to select from specific teams

i then had a drop down of team leaders names in another cell but i want to be able to reference the agents cell to the managers cell.

i've tried an =if statment using the name's option eg

=IF(a1="daves team",Daves_Agents,=if(a1="shauns team",Shauns_Agents,"Change team")

however doesn't seem to work how i would like it to work

[p45cal]

Best attach a workbook with all this in and an indication of the result you want.

[Bob Phillips]

Maybe this will help, How to Make Dependent Drop Down Excel 365 with Video Steps (contextures.com). Look at the Dependent Dropdown Lists video.