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

[Mkeerthi]

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.

 

[louisH]

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

 

[Mkeerthi]

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

 

[louisH]

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.

 

[Mkeerthi]

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.