Searchable Drop down list - Data Validation - For entire coloumn**

Mkeerthi

New Member
Joined
Nov 23, 2017
Messages
17
I have varied requirement

Requirement 1: I have an excel with column A and Column right now with normal Data Validation drop down list
Ex: Column A contains drop down variables like India, China, Japan, US, Indonesia
Column B contains drop down variables like places of respective countries.
Now how to create a drop down list such a way that if I select India in Column A it should show only the places of India in column B. This should work for all the cells in Column A and Column B

Requirement 2: In continuation of the above how to create searchable drop down.
Ex: if I type Ind my drop down should show India, Indonesia.
Such drop down should work for entire cells in Column A and Column B.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

For your first problem :

Create a new sheet called, say "City Names", in which you will leave column A free.
In other columns (B1, C1, D1, ...), put your Country Names (India, China, etc...)
Under each country name put your city names, for example if B1 = "France", then B2 = "Paris", B3 = "Rennes", etc...

To do your dropdowns :

Select all column A, put a list data validation with source :

='City Names'!$B1:$ZZ1

Select all column B, put a list data validation with source :

=OFFSET('City Names'!$A$2:$A$10000,0,MATCH($A1,'City Names'!$B$1:$ZZ$1))

It will say formula in broken blablabla but click OK and it will work.

For your second request i honestly never heard of that in Excel. Hope someone finds an answer
 
Last edited:
Upvote 0
It is not working, Actually Column A is one drop down and Column B is another drop down. and I have maintained Data from C1, D1, E1... I1 i.e country name
then below C1 i.e C2,C3,C4 city names of country C1 and then D2,D3,D4 city names of country D1 and so on.

Have altered the above formula to suit the requirements but I find drop down in column B is showing blank
 
Upvote 0
The dropdown in column B will only display city names when column A is filled.

Try this one in column B (I added a parameter) :

=OFFSET('City Names'!$A$2:$A$10000,0,MATCH($A1,'City Names'!$B$1:$ZZ$1;0))

Make sure you start filling city & country names from col B.
What this formula does :

It takes city names in range 'City Names'!$A$2:$A$10000 (which is blank), and offsets this range from n columns, n is the rank of the country name in range 'City Names'!$B$1:$ZZ$1.
 
Upvote 0
can I fill Country & City names in Column B? If I fill Column B how can a drop be created when it has values in B1,B2,B3. Ideally if Column B is blank only then I can have a excel with required Ex: If I select country name in Column A and come to Column B it should show places of respective Country i.e selected in Column A.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top