dependent drop down list.

Question

Let us suppose, there are 3 states, each state is having 5 districts, each district is having 5 mandals, each mandal is having 5 PO's.

So, here my requirement is that, I should assign the states to cell, once if I selected any state respective districts should come in the next cell and so on till the PO.(Data is for imaginary purpose only.)

Andhra Pradesh	Ananthapur	Guntakal	PO 1
Andhra Pradesh	Tirupathi	Tirupati (urban)	PO 2
Andhra Pradesh	Visakhapatnam	Varadaiahpalem	HPO
Andhra Pradesh	Vizianagaram	Yerpedu	PO3
Andhra Pradesh	Ananthapur	Kudair	PO4
Andhra Pradesh	Tirupathi	Srikalahasti	PO 5
Andhra Pradesh	Visakhapatnam	Sathyavedu	PO 6
Andhra Pradesh	Vizianagaram	Renigunta	PO 7
Andhra Pradesh	Ananthapur	Narpala	PO 8
Andhra Pradesh	Tirupathi	Pitchatur	PO 9
Andhra Pradesh	Visakhapatnam	Pakala	PO 10
Andhra Pradesh	Vizianagaram	Nagalapuram	PO 11
Andhra Pradesh	Ananthapur	Narpala	PO 12
Andhra Pradesh	Tirupathi	KVB Puram	PO 13
Andhra Pradesh	Visakhapatnam	Chandragiri	PO 14
Andhra Pradesh	Vizianagaram	Buchinaidu khandriga	PO 15
Assam	Ananthapur	Guntakal	PO 1
Assam	Tirupathi	Tirupati (urban)	PO 2
Assam	Visakhapatnam	Varadaiahpalem	HPO
Assam	Vizianagaram	Yerpedu	PO3
Assam	Ananthapur	Kudair	PO4
Assam	Tirupathi	Srikalahasti	PO 5
Assam	Visakhapatnam	Sathyavedu	PO 6
Assam	Vizianagaram	Renigunta	PO 7
Assam	Ananthapur	Narpala	PO 8
Assam	Tirupathi	Pitchatur	PO 9
Assam	Visakhapatnam	Pakala	PO 10
Assam	Vizianagaram	Nagalapuram	PO 11
Assam	Ananthapur	Narpala	PO 12
Assam	Tirupathi	KVB Puram	PO 13
Assam	Visakhapatnam	Chandragiri	PO 14
Assam	Vizianagaram	Buchinaidu khandriga	PO 15
Tamil Nadu	Ananthapur	Guntakal	PO 1
Tamil Nadu	Tirupathi	Tirupati (urban)	PO 2
Tamil Nadu	Visakhapatnam	Varadaiahpalem	HPO
Tamil Nadu	Vizianagaram	Yerpedu	PO3
Tamil Nadu	Ananthapur	Kudair	PO4
Tamil Nadu	Tirupathi	Srikalahasti	PO 5
Tamil Nadu	Visakhapatnam	Sathyavedu	PO 6
Tamil Nadu	Vizianagaram	Renigunta	PO 7
Tamil Nadu	Ananthapur	Narpala	PO 8
Tamil Nadu	Tirupathi	Pitchatur	PO 9
Tamil Nadu	Visakhapatnam	Pakala	PO 10
Tamil Nadu	Vizianagaram	Nagalapuram	PO 11
Tamil Nadu	Ananthapur	Narpala	PO 12
Tamil Nadu	Tirupathi	KVB Puram	PO 13
Tamil Nadu	Visakhapatnam	Chandragiri	PO 14
Tamil Nadu	Vizianagaram	Buchinaidu khandriga	PO 15

In first cell it should give the states list AP, Assam, TN like drop down without repeatetion. Once I selected the state then it should give the list of districts as a dropdown and so on.

Answer 1

Thanks for the reply. I tried your solution. There is some error popup coming while trying. So, can you please keep the video for the solution which you have got.

Thanks,

R.M.Lakshman

Answer 2

Here are some articles on creating dependent drop downs

Different Drop Down Lists in Same Excel Cell August 9, 2010 https://contexturesblog.com/archives/2010/08/09...

You can use data validation to create drop down lists in Excel. With a bit of Excel magic, you can create dependent drop down lists, so the selection in one drop down controls what...

Create a Dependent Drop Down List in Excel https://excelchamps.com/blog/dependent-drop-dow...

2017-10-24

A drop down list is an awesome tool. You can pre-define some values and user can enter data by selecting one of those values. One of its advanced features is a “Dependent Drop Down List”. A dependent drop down is all about showing values in a drop down list according to the selection of the value in another drop down.

In the below example, you have two drop-down lists. Size drop down is dependent on product drop down. If you select the white paper in product cell then in size drop down will show small and medium. But, if you select gray paper then its size will be medium and large.

Clear Dependent Drop Down Cells September 11, 2014

https://contexturesblog.com/archives/2014/09/11...

You can use data validation to create a drop down list on an Excel worksheet. For example, show a list of countries, if you click a cell in column B. After you’ve set up that first list, you can make another list, in column C, to show the cities for the selected country.

create cascading drop downs in Excel – video http://chandoo.org/wp/2015/08/31/cascading-drop...

Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.

Answer 3

Hi Machi,

You can obtain dependant dropdown lists by naming data belonging to different states lists (AP, Assam, TN). First dropdown contains states lists. Then you create a second dropdown list referring to first dropdown with following formula :

[CODE]

=INDIRECT(first dropown)

[/CODE]