Query on Dependent Drop Down List ?

[RahulNa]

Hi Guys,

I hope all of you are doing good!

I will explain what I am trying to achieve. I am preparing a contact heatmap for my co. based on certain banks.

We have Region, Sales Name, Country, Bank Name as a drop-down list. I have completed till country, the problem arises with Bank Name since it is mapped according to country. Although no salesperson is going cross-country. I am unable to figure out how to make a drop-down list without having to a named range for each country.

I have a list which contains country and bank name.

 

[Peter_SSs]

Pretty hard to imagine exactly what you have, where and what you are trying to achieve.
Could you post (preferably with XL2BB)a small sample of what data/tables you do have that link these things & explain again in relation to that sample data?

 

[RahulNa]

Hi Peter,

Below is the order of my Drop Down. I am able to create drop-down till Country. The issue is on the Bank Name Drop-Down Since it needs to check the country above it show result based on that.

Region
Sales / Inside Sales
Country
Bank Name

But I have the data in the below format which is a trouble to have each country and create a named range.

Country Wise Bank List

Country	Bank Name
Thailand	Kasikorn Bank
Thailand	Bangkok Bank
Australia	ANZ
Australia	NAB
Australia	Westpac
Australia	CBA
Malaysia	CIMB

 

[Peter_SSs]

I still don't know your layout.
Where is that Country Wise Bank List list?
Is there only one cell that has the Country drop-down Data Validation or many (where)?
Is there only to be one cell with Bank Name drop-down Data Validation or many (where)?

 

[RahulNa]

Hi Peter,

Where is that Country Wise Bank List list? - It's on a separate sheet within the file.
Is there only one cell that has the Country drop-down Data Validation or many (where)? - Yes, It's only one cell.
Is there only to be one cell with Bank Name drop-down Data Validation or many (where)? - Yes, It's only one cell.

 

[Peter_SSs]

OK, IF your Excel 365 has the UNIQUE & FILTER functions (if it doesn't you should very soon as Microsoft's plan was to have them rolled out to all 365 customers in July) then you could see if you can adapt this.

Here is my sheet with the Country wise bank list in columns A:B
The formulas in columns D:E get entered in cells D2 and E2 only as the other values will 'spill' automatically into the other required rows

RahulNa 2020-07-14 1.xlsm
	A	B	C	D	E
1	Country	Bank Name		Country	Bank Name
2	Thailand	Kasikorn Bank		Thailand
3	Thailand	Bangkok Bank		Australia
4	Australia	ANZ		Malaysia
5	Australia	NAB
6	Australia	Westpac
7	Australia	CBA
8	Malaysia	CIMB
9
Lists

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A8)
E2	E2	=FILTER(B2:B8,A2:A8=Sheet1!B3,"")
Dynamic array formulas.

My other sheet with the Data Validation cells for Country and Bank Name in B3 & B4.

RahulNa 2020-07-14 1.xlsm
	A	B
1	Region
2	Sales / Inside Sales
3	Country
4	Bank Name
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=Lists!$D$2#
B4	List	=Lists!$E$2#

After choosing Australia in B3, the Lists sheet now looks like this:

RahulNa 2020-07-14 1.xlsm
	A	B	C	D	E
1	Country	Bank Name		Country	Bank Name
2	Thailand	Kasikorn Bank		Thailand	ANZ
3	Thailand	Bangkok Bank		Australia	NAB
4	Australia	ANZ		Malaysia	Westpac
5	Australia	NAB			CBA
6	Australia	Westpac
7	Australia	CBA
8	Malaysia	CIMB
9
Lists

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A8)
E2:E5	E2	=FILTER(B2:B8,A2:A8=Sheet1!B3,"")
Dynamic array formulas.

And the Bank Name DV cell looks like this