Excel Double-Sorted Drop Down List
May 18, 2021
Thank you for reading the news, and you'll get the next email on June 1st.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
In this data validation drop down list, the 3 most recently ordered products are at the top. The rest of the product names are below those, in alphabetical order.
This technique uses the new functions, SORTBY and MAXIFS, which are available in Excel 365. See more Spill Function Examples on my Contextures site.
The source list for the data validation drop down uses this formula, which sorts the top items first, then sorts the remain products alphabetically, by product name:
=SORTBY(tblProd[Product], tblProd[Sort],1, tblProd[Product],1)
You can get the sample file, and all the setup details, on the Latest Used Items at Top page on my Contextures site.
Here's an obscure Excel problem that I ran into last week. I tried to create a custom number format (link to Microsoft site) for inches, using the double quote mark: 0"
Excel wouldn't allow that, so I used two single quote marks instead. You can see the results in column A, below.
Another option is to use a different Unicode character (Wikipedia). In column C, I used the Modifier Letter Double Prime character (02BA) from the Arial font.
Which custom format do you prefer?
There are more custom number format examples on the Excel Rounding Functions page of my Contextures site.
And if you want a deep dive into ASCII and Unicode quotation marks, take a look at this article by Dr. Markus Kuhn, from the University of Cambridge
Here are a few Excel-related links that you might find useful or interesting.
Dashboard: Chandoo started a new series on his blog - How to create a fully interactive Project Dashboard with Excel. Part 1 shows how to set up a Project Gantt Chart, and more lessons are coming.(Level - All)
Excel Weekend: Sign up for the free online sessions at the upcoming Excel Weekend 7. There are English, Portuguese and Spanish sessions, and the event runs from June 11th - 13th. (Level - All)
Also see: Previous Newsletter Issues
Just in case the garden centres don't open this spring, I ordered an "Easy to Grow Perennial Garden" kit from Costco. It arrived last week, with details on the 8 different plants that are in the kit.
To plan where everything should go, I made a garden map in Excel, and that's why I wanted to use the inch mark! Only the kids know how to cook and garden in metric. ;-)
Planning was the fun part -- the planting took a lot more effort!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210518ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: July 25, 2021 3:49 PM