Dynamic Dropdown in MS Excel 2007

This post will tell you how to create dynamic dropdowns in excel based on a cell’s value.

The example we have taken here is, based on the country selected in the source cell, the dropdown will show the city names in the destination cell. It also covers, how to create a dropdown from the range of values.

Image

 

 

Now let us create a dropdown on the D8 cell (next to Select the country).

1) Go to the Data ribbon >>> Click the Data Validation button as shown below

Image

 

2) Now, Data Validation Box will appear, In the settings tab,validation criteria >> Allow “List”

Image

 

 

3) Now specify the range with values that should come in the dropdown on the cell D8 and click “Ok” as shown below.

Image

 

4) This will give the dropdown with the country names as shown below.

Image

 

5) Now, based on the country selected the city names has to be populated in D9. For which, select the “D9” cell, Go to Data Validation in the Data ribbon >> select select the list as shown above.

6) Type the formula

=IF($D$8=$A$3,$B$3:$E$3,IF($D$8=$A$4,$B$4:$E$4,$B$5:$E$5))

in the range textbox and click ok.

Image

7) So, you will get the desired choices as shown below.

Image

Hope it helps.

Advertisements

One response to “Dynamic Dropdown in MS Excel 2007

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s