YTLE#86: Pivot Table Filtering & Functions

Greetings,

I have a simple pivot table that depicts "By Project" testing progress which then SUMS the number of Pass/Fail test scripts (Row Label = Project | SUM Values = Pass, Fail, No-Run counts)

I'm trying to use a "Report Filter" to only show Projects that were "planned to start" by a given date (another field in my data). I can certainly add the field to to the pivot (Row Labels) but it then adds in additional levels to my Axis (which I do not want).

I am stuck on how to set the filter:
1 - if, i put the filter ("planned to start") at the Report Filter area (using PivotTable Filed List) I am not able to filter using Label Filters

I'm doing a report and would like to only show the most recent sale on a particular product. Is there a way to filter or only show the most recent date for an item. Example of data is:

A1 - Product, B1 - Invoice Date, C1 - Price
A2 - Rubber, B2 - 12/1/09, C2- $10
A3 - blank, B3, 11/5/09, C3 - $9

In this particluar case, I would want it to only show Row A2 for the Rubber, and hide Row 3 since this is not the most recent sale for the item. Thanks

So I am collecting info for test scores and I put the names and info in the pivot table. Is there a way to do a custom sort in the drop field category so I can just show who scored above a certain score?

I am using the following code to filter my pivot table. It hides any data where the date is less than or greater than the specified dates.

VB:

For Each PivItem In PivTable.PivotFields("Date").PivotItems 
    If PivItem.Value < DateFrom Or PivItem.Value > DateTo Then 
        If PivItem.Visible = True Then 
            PivItem.Visible = False 
        End If 
    End If 
Next PivItem 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I also need to filter by my Account and Team fields. My attempt was to have three seperate blocks of the above code for the filtering criteria however when it gets to filtering the account it shows items outside of the date range.

Is there anyway I can nest the above code so that it checks for the date, account and team all at once and then hide it if it doesn't match the criteria?

Thanks

Matt

I have a pivot table that I wish to filter for 2 values; it appears Excel only will filter for one value. The code I'm using is below; the macro runs without generating any error messages, but the pivot table is filtered only for Value1.

Any suggestions/help appreciated.

Thanks, CC

ActiveSheet.PivotTables("LATE_MEDS_6").PivotFields("THER_CODE_1"). _
PivotFilters.Add Type:=xlCaptionContains, Value1:="20:12", Value2:="08:"

I'm working with some sample data for a department store's sales and commissions. I want to have the first and last names and commission amount for each sales person and sort the data ascending by commission. The filter button isn't available, even though I've turned on the "Allow multiple filters per field" option in Pivot Table Options.

What am I missing?

Thank you

Hi All

I need to make multiple pivot graphs from a spreadsheet and would like to know how i can use just the 1 filter so all graphs change to the current id of the filter

so filter at top of sheet

graph1
graph2

graph xx etc

when i select an id in filter all the graphs reflect the data linked to this id

is this possible?

Hi everyone,

Attached is a pivot table containing claims data for motor vehicles.

What I would like to do is that, in report filter say for example, when I select "BMW" in Makes list then under Models list, I'm hoping to create a filter in model list whereby just the models that are under "BMW" will appear. I do not want to see any other models that do not belong to "BMW" appearring in Models list.

So do I have to create a macro module or filter? If so, how do I create them? I hope it doesn't sound too confusing!

hoping you guys can help me out on this one!

cheers.
Yennie

Hi

If I have a simple database in Excel consisting of COST CENTRE, ACCOUNTANT, VALUE for each of 12 months and ACCOUNT NUMBER and I have pulled this into a pivot table where the column area is the 12 MONTHs, the Row Area is the ACCOUNT NUMBER and in the Page Area I have the ACCOUNTANT and COST CENTRE number so that I can filter my data according to accountant or cost centre.

When I apply the accountant filter (in page area) , excel does not automatically limit the cost centre filter to only those cost centres applicable to the accountant chosen. Is there some way that I can link the two filters to that I don't see all cost centres when I have filtered for a single accountant.

Regards

Hi Guy's
I currently have a workbook with 10 pivot tables or so, what i want is a macro that will got through each pivot table and change the pivot chart filter "Agent Name" to a value set by Cell A1 on the front page.

Any help on this would be great.

Thaks