Dependent Dropdown

 Just learned a new trick called dependent drop-down

Two drop-downs where the values in second drop-down depends on the value in first drop-down

It uses data validation and INDIRECT function

In our case we use continents as first drop-down and different countries as second drop-down


Name ranges with their respective headings like continents, Asia, NorthAmerica ….

Quick tip: you need not to go to name manager to name a range it can be done directly by selecting the range and putting name in Name Box adjacent to Formula bar

make first drop-down in any cell e.g. C12 Go to

data -> data validation,  allow -> list , source =continents

make second drop-down where source =INDIRECT($C$12)

INDIRECT function returns the reference specified by the text string

there you are….




Leave a Reply

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

You are commenting using your 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