|I am going to provide the steps for one country. If you have more than one country, you merely have to expand this concept for each one.|
That will means you will have to go one more level deeper with your Named Ranges and Data Validation…List sources.
Please read this carefully and all the way through. It might not make sense until you have read it all the way through and possibly even tried it.
The concept is to break down the list of Districts and Areas into smaller groups using Named Ranges. The Concatenate operator (&) is then used to build a string that the INDIRECT function can use to retrieve a specific Named Range based on what is in the cells that contain the States and Districts Drop Downs.
The following Named Ranges and cell contents are based on the table below.
Note: DD stands for DropDown
A1 contains a Country, in my example India.
A2 contains the DD for the States
A3 contains the DD for the Districts
A4 contains the DD for the Areas
B1:B4 contain the names of 4 States in India
C1:C8 contain the names of Districts. In my example, there are 2 Districts in each State. (I do not know the names of any real Districts, so I made up those names as examples.)
D1:D16 contain the names of the 16 Areas in India. (I do not know the names of any real Areas, so I made up those generic names as examples.)
D1:D4 are Areas in Tamilnadu, D5:D8 are Areas in Haryana, etc. In other words, there are 4 Areas per State.
D1:D2 are Areas in TamDistrict1, D3:D4 are Areas in TamDistrict2, D5:D6 are Areas in HarDistrict1, etc. In other words, there are 2 Areas per District.
The Named Ranges
B1:B4 is Named States
C1:C2 is Named Tamilnadu
C3:C4 is Named Haryana
D1:D16 in Named Areas to include all Areas
D1:D4 is Named TamilnaduAreas
D5:D8 is Named HaryanaAreas
etc. (This breaks down the 16 Areas into 4 Areas per State)
D1:D2 is Named TimilnaduTamDistrict1Areas
D3:D4 is Named TimilnaduTamDistrict2Areas
D5:D6 is Named HaryanaHarDistrict1Areas
D7:D8 is Named HaryanaHarDistrict2Areas
etc. (This breaks down the Areas into 2 Areas per District)
The Drop Downs
A2 Data Validation … List … =States
The 4 States in B1:B4 will be listed
A3 Data Validation … List … =INDIRECT(A2)
The 2 Districts in the State chosen in A2 will be listed
A4 Data Validation … List … =INDIRECT(A2 & A3 & “Areas”)
The list of Areas that will appear will be based on the contents of A2 and A3, with the string “Areas” Concatenated on the end.
When A2 and A3 (States & Districts) are empty, =INDIRECT(A2 & A3 & “Areas”) will return Areas and the Named Range “Areas” (D1:D16) will be listed.
When A2 (States) is populated but A3 (Districts) is empty, =INDIRECT(A2 & A3 & “Areas”) will return A2 & ”Areas” e.g TamilnaduAreas and D1:D4 will be listed.
When A2 (States) and A3 (Districts) are both populated, =INDIRECT(A2 & A3 & “Areas”) will return e.g. TimilnaduTamDistrict2Areas and only D1:D2 will be listed.
I hope I have explained that clearly enough. If not, come and back and I'll try to explain it differently.
A B C D
1 India Tamilnadu TamDistrict1 Area1
2 DD States Haryana TamDistrict2 Area2
3 DD Districts Bihar HarDistrict1 Area3
4 DD Areas Goa HarDistrict2 Area4
5 BihDistrict1 Area5
6 BihDistrict2 Area6
7 GoaDistrict1 Area7
8 GoaDistrict2 Area8
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.