Related drop down list, Dictionary Keys for 3 columns

[moogeedoo]

Hello My Masters & Colleagues, I have 3 columns of data in sheet2 (A) for Branches (B) for Departments and (C) for Names A2:c5000 like the table below the 3 lists are related and has some duplicates and blanks. And I have 3 Columns lets say J,K,L in sheet1 I want to J2:J each cell is validation drop down list from sheet2 (A) without Dup. and blanks, then depending to your choice it's create the validation list from sheet2 (B), then depending to your choice it's create the last validation list from sheet2 (C). any one have a solution for this deadlock, kindly avoid array formulas it's take too much time because the size of data

For example:

[TABLE="class: cms_table, width: 380"]
<tbody>[TR]
[TD]Branch[/TD]
[TD]Dept.[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Lima[/TD]
[TD]Furniture & Interiors[/TD]
[TD]Jackson[/TD]
[/TR]
[TR]
[TD]Lima[/TD]
[TD]Food & Beverages[/TD]
[TD]Emma[/TD]
[/TR]
[TR]
[TD]Jakarta[/TD]
[TD]Real Estate Development[/TD]
[TD]Olivia[/TD]
[/TR]
[TR]
[TD]Dhaka[/TD]
[TD]Real Estate Development[/TD]
[TD]Lucas[/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Real Estate Development[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Fashion wear[/TD]
[TD]Mia[/TD]
[/TR]
[TR]
[TD]Dhaka[/TD]
[TD]Fashion wear[/TD]
[TD]Caden[/TD]
[/TR]
[TR]
[TD]Karachi[/TD]
[TD]Motor Vehicles[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Shopping Malls[/TD]
[TD]Logan[/TD]
[/TR]
[TR]
[TD]Lagos[/TD]
[TD]Shopping Malls[/TD]
[TD]Jayden[/TD]
[/TR]
[TR]
[TD]Lima[/TD]
[TD]Furniture & Interiors[/TD]
[TD]Madison[/TD]
[/TR]
[TR]
[TD]Dhaka[/TD]
[TD]Ceramics & Sanitary ware[/TD]
[TD]Ethan[/TD]
[/TR]
[TR]
[TD]Jakarta[/TD]
[TD]Restaurants & Cafe[/TD]
[TD]Emily[/TD]
[/TR]
[TR]
[TD]Jakarta[/TD]
[TD]Real Estate Development[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you for always support, and excuse me for lingual mistake

 

[MickG]

Have a look at this example file:-
https://app.box.com/s/pgruww0lvmhzbveq0zjz6fse7dlq11eb

 

[moogeedoo]

Have a look at this example file:-
https://app.box.com/s/pgruww0lvmhzbveq0zjz6fse7dlq11eb

Thank you bro. for your kind support and quick reply, unfortunately the code has some issues the validation lists not changed when I change in the data in the sheet2 and when I change the sample data by my basic big data an error stop the code as shown in the attached Pic. I tried some changes in the code lines as much as my knowledge but unfortunately no avail.

 

[moogeedoo]

Have a look at this example file:-
https://app.box.com/s/pgruww0lvmhzbveq0zjz6fse7dlq11eb

Mick, It's working fine when I put my data then save and close the file and open it again the validation lists work fine. but validation lists only in cells J1, K1 and L1 I want validation lists working in each cell in J, K and L except the header J1, K1 and L1. is there any Ideas to do this
Thank you MickG

 

[MickG]

Try this:-
https://app.box.com/s/22y95iyfb7rg0h3d0molm9r0yvwhtu8b

 

[moogeedoo]

Thank you Mr. MickG, It's not giving me errors but it's not working even after I change to my data, the validation lists not loaded, I tried again to change some code lines but nothing new the validation lists not loaded

 

[moogeedoo]

Try this:-
https://app.box.com/s/22y95iyfb7rg0h3d0molm9r0yvwhtu8b

when I open the file and select any cell it gives me Run time error 91: object variable or with block variable not set !!

 

[MickG]

I've just downloaded it and it did the same to me, try clearing the code break, close the file and reopen, that worked for me !!!

 

[moogeedoo]

I've just downloaded it and it did the same to me, try clearing the code break, close the file and reopen, that worked for me !!!

it's working with sample data, but when I change the sample data to my big data it gives me error in sub selection change code. try to change the sample data in sheet2 with any other sample data you have the error will appear to you
thank you Mr. MickG for your kind support

 

[MickG]

I can have a another look if you can Show an example of your BIG data !!!