pasterwish.blogg.se - Filter office 365 contacts to excel

They had an excellent solution to this quandary that I will explain more below, but here is their article: Source: ExcelJet Solution One excellent source for Excel dilemmas like this is the site ExcelJet. I mentioned previously that there was an additional complication with our case, as the results appeared within a column, with multiple values listed for “interest.” So a simple filter would not have yielded results… Special Case: Search/Filter by Text that Contains a Value If you are interested in reading more about=FILTER, here is what Microsoft has to say: Microsoft’s Writeup about =FILTER. The beauty of array formulas!Īnd thanks to Data Validation, adjusting the =FILTER formula will be easy. Notice how, although we built the formula in C2, it extends into the cells beyond. If empty: what value would you like to appear if there are no filter results? Perhaps “No Students Listed”, or even empty quote (“”) to leave blank. Note: because the source data is in a table, the range is named “Favorite Color”, instead of being a column name.For the example file it looks like this: “Source Data”!D:D=’FavoriteColor’!A2 Where would you like Excel to look, and what value is it looking for? In this case, the source data sheet, “Favorite Color” column should equal the value in A2, where Data Validation lives. In this case, Columns A through E in the source data. Array is the cells you want to return if the conditions are met.The anatomy of the =FILTER Function is: =FILTER(Array,include,).

If you have the example file, click into C2, and take a look at the formula bar. This is an array formula, so it will fill into the cells to the right and below where you build the function.Īs an introduction, start with the Favorite Color sheet, a more straightforward example. The =FILTER Function is new with Office 365.

This will be important for the formula step.Īnd voila, there is the dropdown list! On to creating formulas…īy the way, you can read about some more cool Data Validation tricks here: Dynamic Dependent Dropdown Lists.Īlso, we talk about Data Validation in more detail Excel Advanced Formulas training, so I would love to see you in a session if you want to learn more about this feature.

Be sure the spelling matches the source data.

In this case, type the list out, separated by commas.

In the Data Validation popup, change Allow to List, and either list the items you would like to appear in the dropdown, or select cells that contain your list.

In the Data tab, select Data Validation.

Click into the cell where you would like the dropdown to appear.

Start with the dropdown selection that the unique list will reference: So if you are a Qualtrics fan, you will want to know about this! Data Validation I have also seen this sort of situation occur with exported Qualtrics survey results, if multiple responses are allowed. We wanted to create a dashboard sheet with a dropdown list that would allow users to select an interest and generate a list of results. A filter or slicer would not work in this case, as there were multiple potential values for “interest.” A student could list one, or several. We needed to give end users the ability to isolate a list of student names by a specific student interests. This case involved a sheet of Source Data that contained student data: names, details, etc.

Interest: filters source list by interest: this one searches a list of values within the cells.

Favorite Color: filters source list by favorite color.

Here is a copy of the workbook used throughout this writeup: FILTER Function Example Workbook Read on for more information…īefore you do, I want to thank Brittany for this question (along with her patience with several tests and fumbles) for inspiring this byte. We ended up with a sleek custom list on one sheet that, prompted by dropdown selection, pulled data from the source into an array formula. I had to share this with you, because this was a very cool setup. Speaking of unique… a particularly unique case came up recently presenting the opportunity to create a dashboard of sorts using Excel’s new FILTER function. Have you heard about the fantastic new Functions for Excel in 365? XLOOKUP, SORT, FILTER, UNIQUE, to name a few.