We are creating a dynamic drop down list here which is dependent on master drop down list for its values.
- First create you List. In our case we have two lists of items one is list of Cars ( Car 1, Car 2, Car 3) and second is list of Bus ( Bus 1, Bus 2, Bus 3).
- Now Create Name Range for your list which should be your item list name i.e. Car and Bus in our case. For creating Name range select all items of your list and type name of range in name box. Name box is just before formula bar (refer below image).
- Now create first drop down go to Data tab. Select data validation. In Allow drop down menu select List. Select your Master items in Source box, Cars and Bus in our case.
- Create your slave drop down this will change as per your Master drop down. In Allow drop down menu select List. In Source box type formula =INDIRECT(Refer to location of Master drop down cell). In our sample it is =INDIRECT($D$6). Click OK.
Your Auto changing conditional drop down list is ready. Now when you select Car in first drop down list second Drop will only show cars and when you select Bus in will show list of Buses. Download sample sheet click here. We have used it on Excel 2010.