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:

Comments

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Amazing blog and very interesting stuff you got here! I definitely learned a lot from reading through some of your earlier posts as well and decided to drop a comment on this one!

    ReplyDelete
  3. Very informative publish!! Your suggestions are first-rate concerning motivation. Right here you've got posted anything new so I love your web publication. And also your article is unique for one and all readers. Really excellent to know about it. I'm tremendously happy so high-quality.

    ReplyDelete

Post a Comment

Popular posts from this blog

Keyboard Shortcut to add/delete rows or columns in Excel

Excel Keyboard Shortcuts - Handy during day to day work

Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)