In the Source field, you want to select that list of unique departments that I referred to above. (If you are working with a merged cell, you may get a pop-up box asking if you want to extend the Data Validation to those other cells. This will bring up the Data Validation window. To create the data validation list on the Dashboard sheet, start by going to the Data tab on the Ribbon and clicking on the Data Validation icon, which looks like this: In other words, it removes duplicates from a data set.įor the list below, this function is scanning the many entries listed in the Department column of our data table and returning only one unique entry for each department. UNIQUE is a Dynamic Array Formula that returns all the unique values from a data range. The source of this dynamic data validation list has been created using the UNIQUE function. It's a list that we can select from in order to filter attendance data by department. The drop-down list we're looking at today is part of our attendance dashboard. Drop-downs are also called data validation lists, and I've written a post going into more detail about these handy tools that you can check out here: How to Create Drop-Down Lists in Cells. In other words, it can expand or contract depending on changes to the source data. In this post, we're looking at creating a drop-down list that is dynamic.

How to Sort with a Formula in Excel Using SORT and SORTBY Functions.

Return Multiple Values for a Lookup Formula in Excel with FILTER and UNIQUE.

How to Use XLOOKUP for Reverse Order Search.

I recommend you check out the first four posts here:

This attendance report was an entry for the Excel Hash competition. This post is part 5 of a six-part series explaining how to build an interactive dashboard for attendance.

I have also posted a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead. This includes both the desktop and web app versions of Excel. Dunder-Mifflin-Attendance-Report-Final.xlsx DownloadĬompatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365.

Openoffice calc dynamic drop down list