country and city drop-down with dependent in excel sheet

Sometimes we need to add country by selecting drop-down and city drop-down as per selected country for data entry and excel sheet import to any database.

at the time of data entry to excel sheet it may be make spelling mistake, to overcome this probable we can also give a drop-down list to select from this and if any wrong data enter then it will give an error message and prevent to enter wrong data.

Here we will discuss how to add this and make it more easier.

Follow the following steps to make it.

Step 1: open excel sheet

Step 2: add a new sheet and rename it to list (you can give any name instead of list)

Sept 3: Select "list" sheet (second sheet which is renamed in step 2)

Step 4: add countries city as given below format (here we have added limited country and cities)

Step 5: Select all countries to display in the country drop-down and give it name form cell range
     ->Here in our case we need to select A4 to A6 cell (A4:A6)
     ->write country in name range box and press enter key

Step 6: Select all cities of the particular country to display in city drop-down and give it name
             ->Here in our case we need to select C4 to C15 cell (C4:C15)
             ->write country name in range box as per relevant and press enter key (Note: give a country name which is given in country column list, in country namespace and special character will not allowed)
     ->Repete this step 5 for all cities (select all cities and give range name to all sept by step)

Step 7: Select "sheet 1" sheet

Sept 8: add fields title which is used for you, in here we have added "User Name", "Country", "City" fields.

Step 9 : Select country column in our sheet select column "B" .
             ->Then select from menu bar data->validity...->criteria
             ->In allow option select "cell range" from drop-down
             ->In "source" select all countries or write manually in box($list.$A$4:$A$6) then click on ok.

             Note: here $list is sheet name and then its cell range

Step 10: Select city column in our sheet select column "C" .
              ->Then select from menu bar data->validity...->criteria
              ->In allow option select "cell range" from drop-down
              ->In "source" write manually in box(INDIRECT($Sheet1.$B1)) then click on ok.

             Note: here $Sheet1 is sheet name and then its cell range

 Now your excel sheet is ready and you can use it and add country and cities as per selected country from the given list.

I hope it's clear how to display and select city as per selected country dynamically, If you have any query or question about it the you can type in comment box and also gives your review.

You can also make this type of drop-down for menu category and items, parent and children records