Contextures News 20191203

Dynamic Excel Drop Down

December 3, 2019

Dynamic drop downs, Advent calendars, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Dynamic Drop Down

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.

dynamic list of dates

The list is based on a named range -- DateList -- which is on a different sheet.

  • TODAY formula in cell C2 calculates the current date
  • WORKDAY formulas in cells C5:C7 calculate upcoming workdays, by adding the numbers in column B to the current date in cell C2

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.

DatesList formulas

On Contextures

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.

advent calendar

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.

duplicate number sets

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Reminder: Don't miss the free Excel Online Summit. This 5-day event starts on Monday, Dec 9th, with sessions by Bill Jelen, chart expert Jon Peltier, and many more! (Level-All)

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)

Also see: My Excel Products || Previous Issues

Winter Weekend

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.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info


Last updated: December 1, 2019 3:28 PM