Dynamic Excel Drop Down
December 3, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
With Excel's data validation, you can create drop down lists in worksheet cells. That makes it easier for people to enter data, and it helps prevent invalid entries.
Usually you use a list of static items, such as month names or product codes, but for some lists, formulas work better, to create a dynamic list of items.
In this example, people need to select a delivery date that falls on one of the next 3 workdays. Here's the drop down list that appears, if they use the drop down list on Thursday, December 5th. The list shows Fri Dec 6, Mon Dec 9, and Tue Dec 10.
The list is based on a named range -- DateList -- which is on a different sheet.
To show the weekday name, the date cells have a custom number format of ddd mm/dd/yyyy
See more examples of data validation for dates on my Contextures site. There's a video too, and a sample file to download.
Here are a couple of things from my Contextures site and blog.
Advent Calendars: There are a couple of no-macros Advent calendars on my website, if you're counting down the days until Christmas. Get both versions on my Excel Samples page, in the Conditional Formatting section. They're files CF0011 and CF0010.
Duplicate Sets: If you tried the Duplicate Number Sets challenge in last week's newsletter, I've uploaded a new version of the sample file, with 2 more solutions. Thanks to Jonathan Cooper, for sharing his Power Query solution. His sorting step gave me an idea for a simpler formula solution, using helper columns, with SMALL, TEXTJOIN and COUNTIF functions.
Here are a few Excel-related articles that you might find useful or interesting.
Power BI: Sign up for the free PASS Marathon BI 2019 sessions. It starts on December 11, 2019 at 16:00 UTC, and recordings will be available later. Session topics include Chart Design, and Working with Data Models. (Level-Int/Adv)
Did winter howl into your neighbourhood on the weekend? November 30th was cool and pleasant, but on December 1st we had freezing rain, sleet, ice pellets, snow, and probably a few things that I missed! It was windy too, which can cause problems when trees and power lines are coated with ice. Here's the view from my office on Sunday morning. Maybe I'll stay inside until April.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191203ctx.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: December 1, 2019 3:28 PM