Update drop down list based on previous cell using Macros

Hi all,

I have 2 sheets, 1st sheet contains data of city and customer columns and I have more than 200 cities and each city has more than 50 customers.

In 2nd sheet i have same columns and If i select a city from drop down list the customer column drop down list should contain customers names of that particular city..I have done this by creating lists but it is complex for maintaining huge data so i want to implement this using macros.

So, plz help to achieve this using macros.

Here is my sample data

sheet 1

city customer

a ggg
a jkhy
a dgdfg
a wer
ab qefr
ab ergdf
ab dgg
ab yuiyi
c dfg
c dtg
c wes
c wr
c ef

sheet 2

city(drop down) customer(drop down)

a drop down should contain customers of only city 'a'

If i change city to 'b' then customer drop down should contain customers of only 'b'

I want to achieve this using macros can any one reply with macro for this.

Thanks in advance.

Almir's picture

Convert list to Data Tables

Convert list to Data Tables, and it will expand automatically when you add new data to it.