Product drop down on Orders sheet shows latest used product names at the top, with remaining products below that, listed alphabetically. Set the number of top products by typing a number on the Lists sheet.
NOTE: This technique uses the new SORTBY and MAXIFS spill functions (Excel 365). Go to the Excel Spill Functions page, for more examples of what you can do with these powerful new functions.
In this data validation drop down list, the most recently ordered products are at the top. The rest of the product names are below those, in alphabetical order.
You can set the number of "top" items, by typing a number on the worksheet. This technique uses SORTBY and MAXIFS, which are available in Excel 365.
Here's what the drop down looks like, in the Product column on the Orders worksheet.
There are steps below, if you'd like to build the drop down list yourself. You can also get the completed Excel file, to see how it works.
In the sample workbook, on a sheet named Lists, there is a named Excel table, with 4 columns:
Also on the sample file's Lists sheet, there's a cell named TopNum.
In that cell (B2), you can type the number of "top" items that you want to show in the drop down list.
For example, type 3 in the TopNum cell, and:
TIP: To see the steps for naming a cell, go to the Naming Ranges page
To calculate the latest date that each product was ordered, this MAXIFS formula checks the Orders table (SalesData).
The formula returns:
NOTE: The MAXIFS function is available in Excel 2019, and Excel 365. You can read more about MAXIFS on the Microsoft site. That page explains how the function works, and shows 6 examples of how to use it.
There's a simple RANK formula in the DateRank column:
It compares each product's latest date, with all the other product dates, and returns its rank, in descending order.
So, the product with the most recent date is ranked 1, and so on.
NOTE: There could be ties in rank, if multiple products have the same date
TIP: See more RANK examples, and ways to break ties, when needed.
The final column in the product table has the heading, Sort.
Its formula returns a number that's used for the first sort in the product list for the drop down.
Here's how it works:
The List sheet also has a spill formula, in cell B5, to create the sorted product list.
The formula uses the SORTBY function, which is available in Excel 365.
The formula returns all the product names:
Here is the list created by the spill formula, with the top 3, then the other products, sorted A-Z.
If you change the number in the TopNum cell, the product list in cell B5 changes immediately.
In this screen shot, the Top number was changed to 2, so there are 2 items at the top of the list, with the rest sorted alphabetically,
NOTE: The spill formula's list will also update automatically when you select product names on the Orders sheet.
The product list is used for a data validation drop down, on the Orders sheet.
To make it easy to refer to the list, a name, ProdList, was created, using the spill operator
You can see the ProdList name and its formula in the Name Manager.
On the Orders sheet, there's a data validation drop down list in the Product column.
This video shows how to set up a drop down list, using a different workbook as an example. The written steps for this workbook are below the video.
To set up the drop down list,
On the Orders sheet, when you select a cell in the Product column, the drop down arrow appears.
Select one of the recently ordered products, at the top of the list, or scroll down, to find a product name in the A-Z section.
To see the drop down list example from this page, get the Excel Drop Down List Top Items workbook.
The zipped file is in xlsx format, and does not contain any macros.
Last updated: July 13, 2021 3:51 PM