most of us have faced the need for dependent drop downs in out Excel models/Interfaces.
there are many ways to achieve this. i am describing first the simplest of them : using Data Validation, Named Ranges and INDIRECT formula.
scenario:
categories of vehicles - Hatchback, Sedan and SUVs.
required output:
the list of vehicles that belong to these categories must appear against the category under the drop down.
method:
to achieve this,
-> we would have to first define the Named Ranges for these vehicles[Defined on Sheet2 of the workbook]. Remember: you would have to keep the name of these Named Ranges as they are referred to in the cells.
named ranges..
-> on Sheet1, select the cell next to the heading and update the Data Validation criteria. here the formula =INDIRECT does all the magic for you
voila you are done!!!
now this is happening because you are referring to the named range which is exactly same as that of the value in the Cell "A3". now if you change the value in the cell to Sedan to Hatchback , your list will reflect only hatchback cars.
further you can define a drop down even for your categories. i.e: Sedan, Hatchback and SUV can be selected via drop down on column A.
also you can use vlookup formula in the DataValidation to look up the required Named Range so that appropriate drop down is always available the moment you select the value on the left. coz all that is required for DataValidation is a named range to display the list.. so how you get the name of that Named Range there is limited by your imagination and of course by excel formula ;)
cheers,
Som
there are many ways to achieve this. i am describing first the simplest of them : using Data Validation, Named Ranges and INDIRECT formula.
scenario:
categories of vehicles - Hatchback, Sedan and SUVs.
required output:
the list of vehicles that belong to these categories must appear against the category under the drop down.
method:
to achieve this,
-> we would have to first define the Named Ranges for these vehicles[Defined on Sheet2 of the workbook]. Remember: you would have to keep the name of these Named Ranges as they are referred to in the cells.
named ranges..
-> on Sheet1, select the cell next to the heading and update the Data Validation criteria. here the formula =INDIRECT does all the magic for you
voila you are done!!!
now this is happening because you are referring to the named range which is exactly same as that of the value in the Cell "A3". now if you change the value in the cell to Sedan to Hatchback , your list will reflect only hatchback cars.
further you can define a drop down even for your categories. i.e: Sedan, Hatchback and SUV can be selected via drop down on column A.
also you can use vlookup formula in the DataValidation to look up the required Named Range so that appropriate drop down is always available the moment you select the value on the left. coz all that is required for DataValidation is a named range to display the list.. so how you get the name of that Named Range there is limited by your imagination and of course by excel formula ;)
Som
No comments:
Post a Comment