Filter question

[Elsa L.]

Hi,
I have a Pivot Table in Excel 2007 with data. I have a Date column. I need
to filter the data so it shows me the data from 01-01-2008 and today date
minus 1 day. I would like to have the data filtered automatically (without
having to go everyday manually and change the filter date).

I can see some auto filters to filter for yesterday, today, the last month,
year to date, etc. I tried the Between option but it only lets me define
dates, it doesn't let me define references to other cells or formulas (like
=today()-1 for example)...

Can anyone help me on this please?

Thanks in advance,
Elsa

 

[Nick Hodge]

Elsa

Consider a different approach as what you want is not really possible with
simple filtering. Making a few presumptions...

1) You data is in Excel (on another sheet)
2) The date column is column A
3) There are eight columns in the data
4) The sheet on which the data is is called Sheet1
5) The date column is sorted ascending
6) Your start date is the earliest date in the data source (If not you will
need a slightly more elaborate formula)

If you go to the formulas tab and click on name manager. Click 'Add'. Enter
a name for the range, say PivotData and in the Refers To: box, enter

=OFFSET('Sheet1'!$A$1,0,0,MATCH(TODAY()-1,'Sheet1'!A:A,0),8)

Click OK and then go to the sheet with your pivot table. Click in it and
click on the 'Options' tab in the PivotTable Tools tabset. Go to the Change
Data Source button on the Data group and click Change data source... In the
box that comes up type PivotData, or whatever you called your range and
click OK.

Your data will now dynamically change to reflect the data up to yesterday.
(If there is not data for every day you will sometime get no data as the
MATCH function will error if it does not find a match in the data to
yesterday's date.

Hopefully that works for you

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Shane Devenshire]

Hi Elsa,

I assume you are trying to filter the pivot table, not the data area of the
pivot table. Here is one way you can do this:

1. Suppose your dates (in the data area) are in column F starting on row 4.
In the data area create a new field (column), say column G, and enter the
formula
=AND(F4>=DATE(2008,1,1),F4<TODAY()) in cell G4. Fill it down if necessary.
2. Suppose your dates are in the Row Labels area of the pivot table. Add
the new field so that the dates are one level below it.
3. Filter on the new field choosing True.

If you want to be fancy you could change the formula to read:
=IF(AND(F4>=DATE(2008,1,1),F4<TODAY()),"Current")
Then when you filter you would choose Current.

Cheers,
Shane Devenshire
Microsoft Excel MVP