3 Cascading Drop Down Lists with a duplicate reference in 2nd DropDown

[kiran5]

Hi All,
Need your expertise in providing a solution to my problem:

Requirement:
Drop down list to appear in all the three columns in the attached excel file (From A9 to C45) as per the drop down values provided in the file (Refer: A2:G6)

Challenge:
I have different drop down values for the team name - 'Accounts Payable' for both 'Payments' and 'Technology' department and in that case I am unable to get the respective 'Process Names' over there'

Have tried google and forums but not getting the exact solution. Have attached the excel file highlighted with colurs for more details

Thanks much for your support in advance.

 

[GraH - Guido]

Hi
Have a read here on the blog
https://chandoo.org/wp/robust-dynamic-cascading-dropdowns-without-vba/
https://chandoo.org/wp/dynamic-cascading-dropdowns-that-reset/
https://chandoo.org/wp/cascading-drop-down/

There is also an example you can download as reference.

 

[kiran5]

Thanks for the response but my requirement is unique. If you observe that the second drop down value is same(Accounts Payable) but the first column it has different names in the department column. Please refer 4th & 6th rows of the dropdown values. Can you please provide a datavalidation for all these three cascading dropdowns. It would be much appreciated.

Example:
Have shows two examples in 9th & 10th row for a reference. That's is the main trick and challenge as my 'Team Name' column is 'Account Payable' for both 'Payments' and 'Technology' under 'Department Names' column.

 

[bosco_yip]

Hi,

Check attached file and the data validation formula for the 3 cascading dropdown lists

Regards
Bosco

 

[kiran5]

Hi Bosco,
Thanks for this. The formula is working only for the static reference. If I am adding extra rows then it is not working.(Ex: Adding additional team names for Payments department and adding additional Department etc...)
Have included some extra rows after 4th row to add some additional teams for Payments and added few more details in the range. The Department, Team and Process list will keep adding everytime.

Thanks much for a quick repsonse.

 

[bosco_yip]

Hi Bosco,
Thanks for this. The formula is working only for the static reference. If I am adding extra rows then it is not working.(Ex: Adding additional team names for Payments department and adding additional Department etc...)
Have included some extra rows after 4th row to add some additional teams for Payments and added few more details in the range. The Department, Team and Process list will keep adding everytime.

Thanks much for a quick repsonse.

Upload a file with more rows of data as per your described.

Regards
Bosco

 

[kiran5]

Hi Bosco,
Here is the updated file for your review and support.
Have created another spreadsheet for dropdown creation and in the first spreadsheet the list will keep updating for more departments/teams/process names and hence the dropdown formula should accommodate for a dynamic range please.

 

[bosco_yip]

Hi Bosco,
Here is the updated file for your review and support.
Have created another spreadsheet for dropdown creation and in the first spreadsheet the list will keep updating for more departments/teams/process names and hence the dropdown formula should accommodate for a dynamic range please.

See revised file with 2 helper columns

Regards
Bosco

 

[kiran5]

Hi Bosco,
Drop down is not coming-up in the C column of 'Manual_Input' spreadsheet to select the dropdown value even after I input the values in A & B column. Can you please check this for me.

 

[bosco_yip]

Hi Bosco,
Drop down is not coming-up in the C column of 'Manual_Input' spreadsheet to select the dropdown value even after I input the values in A & B column. Can you please check this for me.

Hi,

1] Modified the data validation formula from whole column reference into limited range (1000 rows)

2] See revised file.

Regards
Boso

 

[kiran5]

Hi Bosco,
Its working fine as expected. I really appreciate for your continuous support to me to get the output. Thanks much for this