Originally written by Tim Wilson on May 10, 2011 NOTE: There is an updated version of this post posted. I recommend reading that one rather than this one. Every once in a very rare while, I find myself not motivated to expound upon deep and meaningful subjects. So, this post is not about the, it’s not about my deepening fascination with, it’s not about the perplexing and depressing, and it’s not even about pondering when will have a web site. This is just a good ol’, “Hey, let’s look at a handy capability of Exceland how to use it to the best of its ability.” This came up last week when a co-worker asked me: “How do I get dropdowns working in cells in Excel?” She knew she had done it before, but she couldn’t remember how. In the course of showing her, I realized that, therein, was one of those handy little tips worth sharing. I’m going to walk through three different ways to accomplish this: • The totally common, mundane way — straightforward, but it has limitations • The way I always do it — almost no more effort to implement than the first waybut with fewer limitations • The way I may start doing it (sometimes), which would make the approach just that much slicker Bounce around as you see fit!

Re: Fill out different cells based on value in one cell wasn't really after a blank workbook Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

The Scenario You’re using Excel to enter a table of data, where one or more of the columns have a standard set of possible values. For instance, let’s say you’ve made a list of household chores, and you use that list to both assign a priority to each task as well as to note the status of the work: For both the Priority and the Status column, you’d like to enter the values using a dropdown menu, rather than needing to retype a value in each cell: The wrinkle is that you expect this list to live for a while, and there’s a good chance that you may want to have other values available for either the Priority or the Status columns (or both). We’ll get to that.

The Standard Excel Way — Data Validation The quickest way to set this up is with basic data validation: • Highlight all of the cells that will use the same dropdown values • Select Data » Data Tools » Data Validation • Change the Allow dropdown to List • Enter the values in the Source box (separating different values using commas) • Click OK • Repeat for each set of cells that has a unique set of dropdown value options. That’s all there is to it, and it works. 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. Going in and updating the available list of values is a real pain. That brings us to My Standard Way — Data Validation with 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 ). 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.

Julien May 11th, 2011 I doubt I’ll always remember the formula for your 3rd option. So what I usually do is: – set a tab for all the dropdown values – in column A, for instance, give the name for the list, like tasks – in column B, give the values for the list, just as you did, but without the header in the first row – select the whole column B and define a named range – use that name range in data validation, just like you did. Excal automatically removes the empty values from the name range and I just have to append my next value to the list. Hope this helps. Author's Reply May 15th, 2011 @Matt I’m not sure I understand the “if your named range is needed in other parts of your workbook” comment.

• Call Park is not available. Lync 2010 for mac os x. In terms of known issues, Microsoft has already published a list: One to note here: you can’t have both Lync for Mac and Skype for Business on Mac clients installed side-by-side.