To populate the list based on selection,microsoft excel is providing feature of dependent drop down list.
In this article will see based on selection of country drop down related city drop down should get populated.
Step 1) Create the Country list then select those country and create the named list.
To Create the named list select the values , here Select from cell value from A2 to A4 and then click on the Name Box left of the Formula Bar.Provide name as “Country” and press Enter.
Step 2) Now Create the NamedList for City belong to India as mentioned below. and provide the name as “India”
Step 3) Create the Named list of Austrelia and Germany as mentioned below.
Step 4) Now In Create the First Country drop List
Click on Data Menu -> Data Validation -> Select Settings -> From Allow Drop-down select value as List -> in Source Text box provide value as “=country” and Click on Ok Button.
Step 5) Now Create the Dependent Drop down list.
Select Cell B2 -> Click on Date Menu -> Data Validation -> Select Settings -> From Allow Drop-down select value as List -> in Source Text box provide value as “=indirect(A2)” and Click on Ok Button.
So in B2 Cell now you will see the drop down list for country austrelia or the value which is available in A2
Use of Indirect Function : Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
