#VALIDATION CHECK EXCEL UPDATE#
Now, if you ever want to update values in the list, you can edit the values on the Lookups sheet. Now, it’s the same basic process as described earlier, except, rather than entering the specific values in the data validation Source field, you enter a named range (note the “=” before the named range!):.Repeat this for the other list of values (I named it “List_Status” - I like to prepend the names of similar types of named ranges so that they group easily in the Named Ranges dialog box).Select all of the values for one set of dropdown options and enter a name for that range (in this case, “List_Priority”).Enter the lists of values at the top of that sheet - one list per column.Add a new worksheet (I usually name it something like “Lookups” and then hide the worksheet once everything is set up so it’s never something that the user sees).Using the same household chores scenario, I would accomplish the same end result, sans limitation, as follows: I have a standard way of generating dropdowns that gets around the limitation described earlier: rather than entering the list of values directly in the data validation dialog box, I reference a named range. I regularly use dropdowns to make Excel-based reports more dynamic - enabling the user to choose whether he wants to see a weekly or a monthly version of the report, as well as to select the specific date range (this isn’t so much for the user’s benefit as it is for mine - it means I don’t make a “new report” each week or month, but, rather, update the data in the same workbook and then update the dropdown to get the current report read more about my approach for that in this post). That brings us to… My Standard Way - Data Validation with a Named Range Going in and updating the available list of values is a real pain. The Limitation: Suppose that you decided you wanted to add a new value to the list of options, and that, rather than four cells right next to each other, this same data validation rule was used across numerous non-contiguous cells, even cells across multiple worksheets. Repeat for each set of cells that has a unique set of dropdown value options.
#VALIDATION CHECK EXCEL HOW TO#
This is just a good ol’, “Hey, let’s look at a handy capability of Excel…and how to use it to the best of its ability.” So, this post is not about the latest turn in world of privacy legislation, it’s not about my deepening fascination with two-tiered segmentation, it’s not about the perplexing and depressing indefinite postponement of Demystified Days, and it’s not even about pondering when Team Evil Forces will have a web site. I recommend reading that one rather than this one.Įvery once in a very rare while, I find myself not motivated to expound upon deep and meaningful subjects. NOTE: There is an updated version of this post posted here.
Originally written by Analytics Demystified on May 10, 2011