How do I filter dates by a cell formula in an Excel pivot table?

Question

I am attempting to make some reports in Excel using a pivot table of a Sql server view. One of the customer requirements is to be able to filter the reports based on a date range they select, which seems reasonable. The end user process needs to be fairly simple, so my preference is to have a cell for begin date and one for end date and allow the user to enter these and refresh the table.

There are two problems which I haven't been able to get past:

1. Selecting a date filter doesn't appear to provide any way for me to reference a cell as a source for the value. I have to put it in at the time.

2. It seems as if it is only possible to define a date range if I am grouping on the date field. For some of the reports, this violates the specification. The Report Filter section of a pivot table only seems to allow me to filter by individual values rather than a range. This gets unmanageable on real data which might have hundreds of dates to go through.

One thing I have tried is to create an intermediate table without any aggregates columns in a separate sheet in the same workbook, and then filter that. This hid the columns correctly in that intermediate table, but had no effect on the pivot table which derived from it.

Does anyone have ideas for how I can make this work? It seems like a fairly obvious use case - is there something I am missing about how pivot tables are supposed to be used?

Answer 1

I remember I solved a similar requirement some years ago by making the SQL query dynamic (i. e. putting it together as a string in VBA, and using the cells with the range values in the where condition). ant the VBA macor was then called with a button labeled "refresh".

But I am sorry do not have this code available any more, I just remember the general idea.

Answer 2

The easies thing you can do is changing your pivot table query on the fly.

When the source cell is changed (Worksheet_Changed event), append " AND WhateverDate > '1/1/1111'" to Me.PivotTables("Your pivot").PivotCache.CommandText and refresh the pivot.