This drop down calendar uses a "calculation" sheet and a named range. You can copy drop down lists and paste anywhere in workbook but they have to be in the same order and adjacent.

You can select a year and month and a formula calculates the right number of days in the last drop down list. See picture above.

How I created the drop down calenders in excel 2007:

Create a drop down list for years

  1. Select cell A1
  2. Select tab "Data" on the ribbon
  3. Click "Data Validation" button
  4. Click "Data Validation..."
  5. Select tab "Settings"
  6. Select List in "Allow:" drop down list
  7. Type in "Source:" window
    2011, 2012, 2013, 2014, 2015
  8. Click OK!

Extract dates using a drop down list in excel

In a previous article I came up with a solution on how to extract distinct unique year-month list from a […]

Comments(3) Filed in category: Dates, Drop down lists, Excel

Create a drop down list for months

  1. Select cell B1
  2. Select tab "Data" on the ribbon
  3. Click "Data Validation" button
  4. Click "Data Validation..."
  5. Select tab "Settings"
  6. Select List in "Allow:" drop down list
  7. Type in "Source:" window
    January, February, March, April, May, June, July, August, September, October, November, December
  8. Click OK!

Create a drop down list containing only unique distinct alphabetically sorted text values

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Comments(76) Filed in category: Drop down lists, Excel

Setup Sheet2

  1. Select sheet2
  2. Select cell A1
  3. Type
    =row()

    in cell A1 and then press ENTER

  4. Copy cell A1 and paste down to cell A31

Create a named range

  1. Select tab "Formulas" on the ribbon
  2. Click "Name Manager" button
  3. Click "New..." button
  4. Type
    days

    in Name: window

  5. Type formula:
    =Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$31, DAY(DATE(Sheet1!A1, MATCH(Sheet1!B1, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, 0)+1, 1)-1))

    in "Refers to:" window

  6. Click Close button

Create a dynamic named range in excel

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns […]

Comments(12) Filed in category: Excel, Named range

Create a drop down list for days

  1. Select cell C1
  2. Select tab "Data" on the ribbon
  3. Click "Data Validation" button
  4. Click "Data Validation..."
  5. Select tab "Settings"
  6. Select List in "Allow:" drop down list
  7. Type in "Source:" window:
    =days
  8. Click OK!

Change chart data range using a drop down list (vba)

This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]

Comments(22) Filed in category: Charts, Drop down lists, Excel, Interactive

How to create a list of drop down calendars

  1. Copy cell A1:C1
  2. Paste down as far as needed.

The named range is dynamic, all drop down lists in each row is connected.

Download excel calendar template
drop down calendar.xls
(Excel 97-2003 Workbook *.xls)