Filtering data and validating data in excel
[Update]: Since posting this article on selecting multiple items from an Excel drop down list, I’ve added more articles on this topic.
Here are a few links to updates: In the sample file, there are different examples of the code, with options for where the selected items are placed.
I figured out that the data validation list referred to a named range.
But you don’t want to overload the in-cell drop down list with tons of values, and rather prefer a simpler approach like this: The solution is to use an if() formula to determine which one of the two ranges should be used to validate cell contents. Next you learn how to sort a data range on one column or on multiple columns and how to use a custom sort order, the Filter (Auto Filter) feature and the Advanced Filter feature to view data that meets specific criteria.Then you learn how to add and remove subtotals from a data range while viewing the data range in outline form.Assuming the list of projects for each department is in a range B10: C22 with column B having the department name and column C having the project name and the list is sorted on column B, we can use offset() and match() combination along with countif (ahem! ) to determine which range to use for project cell drop-down. You now have a list drop-down that changes values based on what is selected in an earlier cell.