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:
http://blog.arvindkumar.com/2012/10/power-of-data-validation-1.html
http://blog.arvindkumar.com/2010/05/custom-validation-in-excel.html
http://blog.arvindkumar.com/2012/03/data-validation-in-excel.html
Location: Kolkata, West Bengal, India

Comments

  1. PrashantFebruary 25, 2013 at 5:50 PM

    This comment has been removed by a blog administrator.

    ReplyDelete
  2. App Development BangaloreJanuary 11, 2017 at 12:10 PM

    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. Best essay writing serviceAugust 31, 2017 at 4:22 PM

    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

Image
" Ctrl + + " (add rows or columns) and " Ctrl + - " (delete rows or columns) are the easy shortcuts one may remember while adding and deleting rows/columns in Microsoft Excel. Adding rows or columns: If you wish to add rows above the current cell... just press "shift + spacebar" (discussed in my earlier post) to select the current row and then press "Ctrl + +". It will insert a row over the current row. Press "Ctrl + +" repeatedly to insert as many rows. Similarly, "ctrl + spacebar" and then "Ctrl + +" adds a column before the current column. You may try pressing "Ctrl + +" without selecting a row or a column. In such a case, you may get a pop-up (as shown in the image) where you can choose an option of adding a row or a column. Deleting rows or columns: "shift + spacebar" followed by "Ctrl + -" deletes the selected row & "ctrl + spacebar" followed by "Ct
Read more

Excel Keyboard Shortcuts - Handy during day to day work

I've had few posts earlier on few excel shortcuts. Navigation powered by "Ctrl" in Excel Shortcut to insert current date in a cell Shortcut to AutoFilter So, here I am, back with some more interesting shortcuts which will make your excel spreadsheet experience simpler. Let me start with some "CTRL" combinations which will be very handy for formating cells to match your requirements: CTRL + 1 : Opens the "Format Cells" dialog box CTRL + 2 : is to make the contents of the cell BOLD (this is a toggle key, to unbold use same CTRL + 2) CTRL + 3 : is to make the contents of the cell ITALICS (this is a toggle key) CTRL + 4 : is to underline the contents of the cell (this is a toggle key) CTRL + 5 : is to strike through the contents of the cell (this is a toggle key) CTRL + 9 : is to hide the entire row (to unhide use SHIFT + CTRL + 9) CTRL + 0 : is the hide the entire column (to unhide use SHIFT + CTRL + 0) CTRL + ~ : is to show or
Read more

Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)

Image
We already know the primary challenge of using SUMIF or SUMIFS i.e. unable to sum more than a single column or row as a sum range. Many of us in the course of our excel usage may have experienced that SUMIFS has a bigger disadvantage when used across workbooks i.e. the source workbook should be open inorder to enable to computation in the destination workbook. If the source workbook remains closed, the result in the destination workbook will be "#VALUE". In my previous post , the alternative suggested not only does overcome the  "#VALUE" challenge but also allow users to sum a larger range.  In this post, I was able to decipher yet another alternative. I'm sure expert users may already be using this however to help other users, this post can be a ray of hope. We may have used the SUMPRODUCT function to calculate weighted average. Extrapolating the usage of SUMPRODUCT with condition or conditions can help you achieve the SUMIFS goal.  Syntax:  =SU
Read more