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") 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