## Extract dates using a drop down list in excel

*Article updated on December 20, 2010*

In a previous article I came up with a solution on how to extract distinct unique year-month list from a date series. In this article I will use this unique distinct list in a drop down list, making it easy to visualize alla data in given month and year.

Here is a picture of a distinct unique year-month list (Column E), extracted from a date series (Column A).

**Create a drop down list
**See drop down list on cell A19 on picture below

- Click Data tab
- Click Data validation button
- Click "Data validation..."
- Select List in the "Allow:" window. See picture below.
- Type =$E$2:$E$15 in the "Source:" window
- Click OK!

Formula in A22:

copied to the right and then down as far as needed.

**Download example workbook**

Download excel sample file for this tutorial.

extract-dates-using-a-drop-down-list-in-excel.xls

(Excel 97-2003 Workbook *.xls)

Functions in this article:

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**TEXT(**value, format_text**)**

Converts a value to text in a specific number format

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]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 […]How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]### 3 Responses to “Extract dates using a drop down list in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

hi guys!

i've recently trying to crack this to work. (https://www.mediafire.com/file/zwyt4zizvtw/abc.xlsx)

basically, the source table that i have is in COlumn A-N.

1) Column A - dates

2) Columns B-N - Products

The values are the qty sold for each product on each date. this is some sort of product-profiling to know which product is "popular" and which is not.

this table will be expanded on a daily basis.

Now, profiling (charting) ALL dates would be useless. Therefore, i've decided to chart it by monthly instead (Jan, Feb, etc.).

My first attempt was to create index from a dropdown list box (cell P2 and Q2). The year & month listing is generated in a crude way (column AF and AG).

Since there are 13 products (some has very high values compared to others), therefore the values itself are separated into 4 different charts (will be done manually by me, dont worry!)

the charts' data source (after "selected" which year and month from the dropdown list boxes) is located on columns R-AE.

1) Column R = list of dates that corresponds to the year-month selected (dropdown list). If i selected 2009 & December, the dates here will list down 1-Dec-2009 ..... 31-DEc-2009. *tricky part is some month have 31 days, 30 days and 28/29 days).

2) Column S-AE = values that corresponds to the products VS date in column R.

Question 1: To cut it short, how do i extract only values for a particular month from a big list?

Question 2: How to create unique distinct year and months from a long date listing (column A)?

i.e. If i select year 2009, the "months" will only show Oct - Dec (Jan - Sep not exist). or if I select year 2010, only month Jan & Feb will show (Mar - Dec not exist).

thanx!!

David,

Question 1,

Is it possible to use Excel 2007 table? See this post:

https://www.get-digital-help.com/2010/02/11/match-two-criteria-and-return-multiple-rows-in-excel/

David,

Question 2, See this post: https://www.get-digital-help.com/2010/02/18/create-unique-distinct-year-and-months-from-a-long-date-listing-in-excel/