One Drop down List Dependent on Other
For example in first drop down, we select the type of food, i.e. American or Pakistani. And the second drop down shows us a list of respective food items.
To do this, we need to do the following easy steps.
On the second sheet of our workbook, create named ranges of following.
American
|
Pakistani
| |
Pasta
|
Chicken Karahi
|
Pakistani
|
Sausage
|
Naan Cholay
|
Creating a named range is very easy, for this go to formula tab and click on Define name. If you cursor is in the header row of column the box will pick it. In the “refers to”, add the range excluding the header row.
This way we will create three named ranges.
Now go back to sheet1 and in Column A1 & A2, add labels/heads like Type of Food and Food Item.
Have your cursor in row A2 and go to Data tab and click on Data Validation.
In the Allow box, select list. And in source box refer the named range of Type, like the below snapshot.
Now go to row B2 and, like we did earlier, go to Data tab and click on Data Validation.
In the Allow box, select list. And in source box enter this formula.
=INDIRECT($A$2)
And we are done. Now, the drop down values of second column, are dependent on first one.
Click here to download the example dependent drop down sheet
ReplyDeleteExcel is a software program from Microsoft basic excel for beginners that is a part of Microsoft Office. Excel calculations is compiled for making and altering spreadsheets that are spared with a .xls expansion. It's general uses in corporate cell-based figuring, turn tables, and different diagramming devices. With an Excel spreadsheet, you could make a month to month spending plan, track costs of doing business, or sort and compose a lot of information calculations in spreadsheets of Excels.