Thursday, January 3, 2013

Power of Data Validation - 2

Have you ever wondered if we can have a dynamic drop down list i.e. a drop down using data validation that gets updated automatically with any change in the source of the drop down...

I often faced problems having a dynamic drop down list but ultimately I was able to find a solution to my problem.

Step 1:

Format the source list as a table and name the table (here I've named the table as "Fruits")


List




Step 2:

Go to Formulas -> Name Manager and click on New



Define any name in the dialog box

In the example, I've named it as "Names". Then click on Refers to field and select the table in the excel sheet. The refer to will appear as =TableName[ColumnName]

Step 3:

Now create the drop down list using data validation and mention the source as the name you defined. In this case "Names"


Here you go. The drop down list hence created is dynamic. Any addition or deletion in the source table of the drop down options, the validated cell gets updated automatically...!!

Excel is great!

Related posts: