Any Pivot Table experts? Filter by Date Range

[elayem]

Hi All,

None of the search results have come up for me on this. Is there any way to automate filtering a Pivot Table by a date range?

For example, I have data in Column A w/ Dates, e.g. November 12, 2012 and Values in Column B. 1, 2, 3, etc. There may be multiple data on a particular date (i.e. 5 more rows with November 12, 2012 and corresponding data).

I wanted to set up 2 cells where users can input the Start Date and End Date and have the Pivot Table automatically reflect this date range. Is this possible? E.g. User will input November 30, 2012 in cell D1 and December 5, 2012 in cell E1.

Thanks in advanced,
Elayem

 

[elayem]

Bump. Doable? Impossible?

 

[elayem]

Bump.

 

[elayem]

Bump! =(

 

[elayem]

5th time's a charm!

 

[Fazza]

hi

Here is one way. For this set up of data. Cell A1 "Dates". Cell B1 "Values". Cell D1 "Start Date". Cell E1 "End Date". Data below headers in contiguous blocks - so no blank rows. Dates & values from row 2 to row xx. Start date in cell D2. End date in cell E2. Create normal defined name ranges for the tables. Range "A1:Bxx" name MainTable. Range "D1:D2" name Start. Range "E1:E2" name End. Save & close the file.

From a new file, ALT-D-P. Choose external data source. Get Data. Excel files, OK. Choose the file, OK. See Add Tables. Select one table to add, then close the dialog box. Now in MS Query hit the SQL button and change whatever is there to

SELECT M.Dates, M.Values
FROM End E, MainTable M, Start S
WHERE M.Dates >= S.[Start Date] AND M.Dates <= E.[End Date]

OK to enter that, now see the (date filtered) results set. Hit the 'open door' icon to exit MS Query. Then choose finish & set up the pivot table how you want it. If you want you can open the source data file & the worksheet containing the pivot table can be moved into that file.

Now change the start and end dates then refresh the pivot table. If you want you could add a little VBA so that the pivot table refreshes on change to either start or end date input cells.

HTH

 

[elayem]

Hi. What file type am I saving the original data? When I am browsing for that file later on, it doesn't show up because it's only searching for data files.

 

[Fazza]

I don't know what file type you're saving the original data as. I saved it as an Excel file.

If it is not an Excel file, what file type do you want?

 

[elayem]

I want excel, but during this step:

From a new file, ALT-D-P. Choose external data source. Get Data. Excel files, OK

It couldn't find the excel file... Let me go back to check.

 

[Fazza]

PS

I'm using Excel 2003. Maybe there are slightly different steps in newer versions. Suggest you google for examples.

For example, I just found this Microsoft information for Excel 2013
http://office.microsoft.com/en-001/excel-help/create-a-pivottable-to-analyze-external-data-HA102895232.aspx

regards