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 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
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
February 15th, 2010 at 11:45 am
hi guys!
i've recently trying to crack this to work. (http://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!!
February 17th, 2010 at 10:30 pm
David,
Question 1,
Is it possible to use Excel 2007 table? See this post:
http://www.get-digital-help.com/2010/02/11/match-two-criteria-and-return-multiple-rows-in-excel/
February 18th, 2010 at 10:05 pm
David,
Question 2, See this post: http://www.get-digital-help.com/2010/02/18/create-unique-distinct-year-and-months-from-a-long-date-listing-in-excel/