Article updated on August 28, 2017

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 […]

### 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 […]

### 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

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

### 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) […]

### 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.