Filter for Pivot Table

[kaken6]

Hello everyone,
I've used these google groups a couple times in past month and you've
all been really helpful. I hope I will be able to contribute
sometime, but alas, it looks like I have one more question, if anyone
could help that would be awesome!

So, what I'm wanting to do, is get the total for different categories
of a list of imported data which may range from 2,000 cells to I
guess, well, 65,536 cells (or whatever the maximum on office 2003 may
be). Looking at other suggestions made, it seems the common consensus
is to use a pivot table. I tried this, and it worked great - aside
from one problem - I'm only wanting to use data filtered between two
dates, and when I use the pivot table, it disregards my auto filter
and takes all the data.

The only thing I can think of is use an advanced filter, and have the
data copied to another worksheet which is subsequently referenced for
the pivot table. The problem that lies here, is that I'm designing
the program to be as user friendly as possible, as I will not be the
one running it in the future. With the auto filter, it was easy to
select the dates one wanted to filter between. Just go to Custom> and
pick 'greater or equal to' the first date, and 'less than or equal to'
the second one. When I try to use an advanced filter this seems more
complicated. What would make this a lot easier is if I could
reference the two dates in the Criteria Range to be copied (on another
worksheet) but I haven't had success doing this. Is it possible to
have under the criteria range something to the effect of being between
the two referenced dates? Also, is this possible to automate with a
macro, so that whenever I open the worksheet, it advanced filters and
sends the data to the other worksheet to be referenced? I've had
success in auto filtering data in which only data with a "yes" is
displayed, but this is a bit over my head.

Thank you very much!

 

[herbzee]

I too have the same situation, in addition I only get part of the Pivot
Table and NEVER get an OK prompt so that I can't even get as far as Kaken6
Cheers-Herb.

 

[Debra Dalgleish]

You could enter start and end dates on the worksheet, then show records
from that range in the pivot table. There's a sample file here, that
uses a bit of programming:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'PT0014 - Filter from Worksheet Date Range'

 

[Terry Costello]

I have a Invoices payable spreadsheet data page with the fields Net,StDate, EndDate,Days,AmtPerDay,Expense
and a Pivot Table page Sheet5. The pivot table generates a list of expenses used up or accrued during the specified date period ie EndDate+1 - StDate. The Pivot table should display only the amount of expense used up in the date range ie EndDate+1 - StDate that is defined by the user when
entering in the stDate and EndDate filters on the Pivot Table.

1. Is it possible to filter records in a excel data worksheet by 2 Date field filters ie EndDate + 1 - StDate which are columns in the data worksheet with the StDate and EndDate defined by the user in the Pivot Table page.

2. Is it possible for a pivot table to look at each
row and for the records that are in the date range calculate the number of days between StDate and EndDate that are in the date range
ie EndDate +1 - StDate for each row that has at least 1 day or more in the date range specified in the Pivot Table.

3. For invoice payable items in the data page ie invoicesOutstanding Is it possible for a pivot table to Multiply Number of days in the
date range range by the amtPerDay figure?
Do Days and AmtPerDay need to be in the spreadsheet
For instance is having Net(amount)StDate & EndDate
fields(Columns) enough. Can AmtPerDay & Days(within the date range) be calculated by the pivot table?

3. Is it possible for a pivot table to display
total expense for each expense category that has had expense incurred in the date range entered by the user on the pivot table filter? ie DaysInrange * AmtPerDay for each row and sum different records in same expense category.

The whole idea is to put all of my business's invoices & other expense items into a data table
worksheet where the amount, start date end date
& expense category are entered along with possibly days in range & amt per day if they are not calculated fields. the idea is to enter a start date and end date in the pivot table and then the pivot table displays the expense types and the total amount of each expense accrued or used up
during & including the StDate and the EndDate.
I have created a pivot table but it only extracts records with both the stDate and the EndDate entered and even then it displays the whole amount of expense item even if some of the amount is used up outside of the stDate and End Date entered by the user in the Pivot Table report.

 

[Shane Devenshire]

And what's the question?

SD

 

[Shane Devenshire]

Hi,

Some answers in line ---

I have a Invoices payable spreadsheet data page with the fields Net,StDate, EndDate,Days,AmtPerDay,Expense
and a Pivot Table page Sheet5. The pivot table generates a list of expenses used up or accrued during the specified date period ie EndDate+1 - StDate. The Pivot table should display only the amount of expense used up in the date range ie EndDate+1 - StDate that is defined by the user when
entering in the stDate and EndDate filters on the Pivot Table.

1. Is it possible to filter records in a excel data worksheet by 2 Date field filters ie EndDate + 1 - StDate which are columns in the data worksheet with the StDate and EndDate defined by the user in the Pivot Table page.

You can filter a data sheet on a date range using the Custom option under
Data, Filter, AutoFilter, however, the pivot table will ignore this and
retrieve all the records. In the pivot table you can filter on a date range
if you are using 2007. In 2003 you would need to hide all the dates you
don't want displayed.

2. Is it possible for a pivot table to look at each
row and for the records that are in the date range calculate the number of days between StDate and EndDate that are in the date range
ie EndDate +1 - StDate for each row that has at least 1 day or more in the date range specified in the Pivot Table.

You can create a calculated field - assume you have a EndDate and StDate
field, then with the pivot table active choose PivotTable, Formulas,
Calculated Field and give the new field the name Diff in the second box
highlight the default entry and then double-click the EndDate field in the
list and type +1- and then double-click the StDate field. Click OK. Place
it whereever you want it. Note the format may be a date so change the format
of the field to Number, 0 decimals.

3. For invoice payable items in the data page ie invoicesOutstanding Is it possible for a pivot table to Multiply Number of days in the
date range range by the amtPerDay figure?
Do Days and AmtPerDay need to be in the spreadsheet
For instance is having Net(amount)StDate & EndDate
fields(Columns) enough. Can AmtPerDay & Days(within the date range) be calculated by the pivot table?

Same idea as the previous example. (it is often safer to do calculations in
the data range as new columns and then to incorporate those into the pivot
table)

3. Is it possible for a pivot table to display
total expense for each expense category that has had expense incurred in the date range entered by the user on the pivot table filter? ie DaysInrange * AmtPerDay for each row and sum different records in same expense category.

Not clear on this one, sorry

The whole idea is to put all of my business's invoices & other expense items into a data table
worksheet where the amount, start date end date
& expense category are entered along with possibly days in range & amt per day if they are not calculated fields. the idea is to enter a start date and end date in the pivot table and then the pivot table displays the expense types and the total amount of each expense accrued or used up
during & including the StDate and the EndDate.
I have created a pivot table but it only extracts records with both the stDate and the EndDate entered and even then it displays the whole amount of expense item even if some of the amount is used up outside of the stDate and End Date entered by the user in the Pivot Table report.

If this helps, please click the Yes button

Cheers,
Shane Devenshire