Creating Custom Pivot Table Report Filter

Aug 8, 2012

I was wondering if it is possible to create a custom pivot table report filter? I would like to take an existing pivot table report filter and manually add values into it. I would like to do this because I have multiple pivot tables, some with the same values and some with different values and I have a VBA code from Contextures that applies a mass filter to all fields with the same name. So if i could manually add values into one report filter, I could filter from one location and have all my pivot tables update at the same time if they contain the value that i would like to filter by.

View 1 Replies


Locking Pivot Table But Not Report Filter?

Aug 29, 2013

So I was wondering if it were possible to completely lock a pivot table, almost like protecting the sheet, but to allow the report filter field at the top of the table to be used. So what I'm trying to show is our net rev accounts in the rows & quantity and amounts as the value all fixed and unable to move about or drill down; but then have our product titles at the top in the report filter which you can still use to be able to select specific products.

I understand that I could make a big old page of drop down lists and vlookups and sumifs etc etc, but surely the above would be way quicker, plus I wouldnt have to bother checking it all works regularly!

I'm currently running this macro, which nearly does the job, but the data and row labels are still able to be manipulated. So... changing the macro, or doing something completely different?

Sub KillPivot()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)


View 1 Replies View Related

VBA To Manipulate Pivot Table Report Filter

May 28, 2009

I have a pivot table created from a data table with three columns: Date, Sales, and Customers. I have the Date column in the Report Filter and I want to change the date based upon a value in a cell range named Date, of all things. The pivot table is located in another worksheet and the range Date is changed by a spin button in the active worksheet.

My code is:

Sub ptDate()

Dim pt As PivotTable
Set pt = Worksheets("Pivot Tables").PivotTables("PivotTable1")

pt.PivotFields("Date").CurrentPage = Range("Date")

End Sub

View 9 Replies View Related

Create Report Using Power Pivot While Creating Relationship Between Linked Table?

Apr 24, 2014

I want to create a report using power pivot while creating the relationship between the linked tables, power pivot is throwing error "The relationship cannot be created because each cilumn contains duplicate values. Select at least one column that contains only values"

View 1 Replies View Related

Report Filter In Pivot Table Based On Another Field From The List?

Feb 20, 2012

Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.

View 2 Replies View Related

Pivot Table - Grouping Items (Date) In Report Filter?

Nov 11, 2013

I have a pivot table with the following items;

Report Filter = Project Names & Dates (filtered on 2 fields)

then the pivot table of data shows
Dept, Sum of Hours, Sum of Total ($), Sum of Days

When you click on the drop down to adjust the filter for the Date it gives you the whole list of dates, day by day as it is in the source data.

Is there a way to make it in the filter by month and year, the way it would in an ordinary filter. So if I wanted to have the pivot table show only the values with an October date I can, rather than having to deselect all and then individually check the boxes for the 1st through to the 31st of October.?

View 2 Replies View Related

List Box To Select Multiple Items In Report Filter Of Pivot Table?

Jun 19, 2013

I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.

When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)

View 2 Replies View Related

Change Pivot Table Report Filter From Userform Textbox / Combobox

Aug 17, 2013

I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using combobox on userform.

Sample Data


Smith, J.

[Code] .....

Sub CreatePivot()
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Employees Data").Select

[Code] .....

Private Sub ComboBox1_Change()
ComboBox1.Value = objTable.PivotFields("DOH")
End Sub

View 1 Replies View Related

VBA Code To Change Pivot Table (Report Filter) From Userform Textbox / Combobox

Aug 16, 2013

I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. I've attached an excel file as an example.

View 2 Replies View Related

Excel 2007 :: One Pivot Table - Multiple Graphs Based On Report Filter?

Jan 10, 2014

I have a perfectly working pivot table and I would like to make some graphs based on the report filter. My report filter has 4 categories, with each more than 10 sublevels.

When I make one pivot graph/chart, this goes fine, the data is ok, and I am happy. But one I make a second, and thus adjust the report filter, the first graphs changes according to the filter. I dont want that to happen

Ultimately I would like a powerpoint presentation with multiple charts, based on one table, with different report filter filters. Updated ONLY on the values, not the filter.

View 4 Replies View Related

Pivot Table - Show Report Filter Pages Shortening Tab Names Even When Character Is 31

Mar 4, 2014

I have an issue with a Pivot table: I am using the 'show report filter pages' function and prior to this I have ensured my character length of that field is equal or less than 31 however when I hit the function the tab names are shortened to 26.

View 1 Replies View Related

Excel 2010 :: Pivot Table Add Calculated Field Based On Certain Text In Report Filter

May 29, 2014

Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.

I used the formula below in a calculated field and it does not match the values using the added column to the data file.

=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)

I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.

View 2 Replies View Related

Excel 2010 :: Changing Pivot Table Report Filter Via VBA With Named Range Or Array

Jul 21, 2011

I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.

My code thus far:

Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").

I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.

When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:

For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False

When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.

Here's the corresponding code for that:

For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True

I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).

I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.

(Using Excel 2010, Windows 7.)

View 9 Replies View Related

Pivot Tables / Report Filter

Nov 10, 2008

i have a pivot table report filter that i cannot sort a-z. i've tried sorting the source data and also right clicking on the first value then choosing display a-z - neither work.

View 5 Replies View Related

Excel 2010 :: Report Filter For Pivot Not Working With VBA?

Apr 5, 2013

I have a pivot table that I want to update via VBA based on dates user inputs on an input tab. (Want to use the Between function)

The Date field is in the Report Filter section (as I don't want it in the row or column area), but when I click on the dropdown arrow, I don't get any option to filter the date at all. I've double checked that the dates in my datasource are formatted for a pivot table, but that doesn't seem to be the issue.

When I use code I've found on this and other sites, it also doesn't work. When I run it, I get the following error at the "If Date DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then..." line

Error Message: Unable to set the Visable Property of the Pivot Item Class

Sub Test()
Const StartDate As Date = #1/15/2013#
Const EndDate As Date = #2/13/2013#


View 9 Replies View Related

Pivot Tables - Show Report Filter Pages

Mar 3, 2014

By using this function I have managed to do a 1000 sheet report, 1 for every customer.

However, my problem is that I don't know a way of globally setting the print area for every single sheet.

Also, the reports are not exactly the same size, they will vary from 5 to 12 columns.

View 1 Replies View Related

Pivot Report Filter Doesn't Hide Non-Relevant Rows

Feb 3, 2014

So I have a pivot table of about 2000 rows with data on employees.

In the table, there is a field associating each employee with a location (midwest, southeast,where ever) and I have a report filter on it that lets me view only people from a given region.

It was working fine, but I'm doing a lot of VBA macro related to the pivot, and I think I somehow changed a setting somewhere in the process and don't know how to reverse it.

When I apply the filter now, it only shows data for people in the region i choose, except it still shows all the other lines that should be hidden, just with no values. Here is a crude visual

Name region sales


(jerry and aaron should not be visible at all)

View 2 Replies View Related

Pivot Table With 2 Report Filters

Apr 16, 2013

I am trying to arrange data (pay by week) for regions and sites within a region. In the first Report filter, I select the site, and the second report filter gives me all of the sites - not just those in that region.... How I can either arrange my data or change my pivot, so only the appropriate sites show under the region?

View 1 Replies View Related

Generate New Report From Pivot Table?

Jun 16, 2014

I have a pivot table based on sales data and I need to know the 3 salespeople that had the highest sales during the current fiscal year versus the last fiscal year.

I have tried manipulating the pivot table but I keep getting the error message: "the following system error occurred: the requested name is valid but no data of the requested type is found".

View 1 Replies View Related

Pivot Table Report With New Headings

Sep 28, 2007

I have a simple table Attached. (A simplified version of a much larger table) The data continues on for thousands of entries and there are quite a few more columns. Names are dynamic in that names will be added and deleted over time. (There is a list of names on a different worksheet that updates as users are added.)

I wish to Consolidate the Data Under Different Titles. The data in the NAME column will Consolidate to list each name in Alphabetical order. The data under the RAP COUNTER column should count the number of YES entries per NAME under the new title ATTEMPTS. The data under the RESULT column should Count the number of KILL entries under the new title KILLS. And the Data in the TG SCORE column should average all entries per NAME under the new title TOTAL SCORE. I tried to do this with a pivot table to no avail. I could write some programming to accomplish this, but I am am amateur, and I also worry about time required to run a macro for thousands of lines.

View 5 Replies View Related

Pivot Table Report - Total Profits

Dec 16, 2013

I am trying to create a pivot table report to find out how much profit each client has made and I need the total profits to be added up for each client so I can see who has made the most/least.

I have attached a sample ss.

View 3 Replies View Related

Multiple Report Filters In Pivot Table

Apr 5, 2013

Below is a simplified/truncated version of a data set that I am using in a pivot table:

Client Asset Return
1 Port1 10%
1 Port2 12%
1 Port3 11%
1 Port4 13%
1 Port5 10%

[Code] .....

I have created a pivot table and I'm using report filters for both Client and Asset (obviously there are a lot more data points). When I filter on Client 1 I would expect to only see Port 1 - Port 7 available in the Asset filter however, I see every asset in the data set. I need to pick and choose using the Select Multiple Items check box without having to scroll through every single asset. Is there a way to easily do this?

View 1 Replies View Related

Pivot Table Detail Report Format

Jul 27, 2004

When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number.

Is it possible to preserve the format of the raw data file that has been used to create the pivot table output?

For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost.

I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up.

If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.

View 9 Replies View Related

Format Data For Pivot Table Report

Dec 27, 2007

I am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want.

Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel.

Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.

View 2 Replies View Related

Pivot Table - Counting User Once Regardless How Many Times Their Name Is In The Report

Dec 11, 2013

I have a big list of names of people that have completed a bunch of training they have completed. I want to be able to run a pivot table/chart and only count a user once vice each time they have completed a training. So in otherwords I want to get a count of how many people are on the list vice how many certifications they have.

View 1 Replies View Related

Macro To Report How Many Values Are Returned In Pivot Table

Jul 15, 2014

I'm trying to find a macro that will report how many values are returned in a pivot table. For example, if there is 5 values returned, I want it to return the value "5" in a cell If there are 10 values returned from the pivot table, I want to return the value "10" in a cell

View 1 Replies View Related

Pivot Table Report Was Saved Without Underlying Data?

Aug 19, 2013

I have a file with lots of pivot tables which works fine. But if I save and reopen the file then I get the following message when I try to change a filter on any of the pivot tables:

"The pivottable report was saved without the underlying data"

(ideally without refreshing all pivot tables using a workbook open event as this will cause a 10-20 second time-lag)

p.s. the file used to work fine before I added an extra pivot table (there are roughly 10 pivot tables in the file)

View 1 Replies View Related

Apply A Filter In A Pivot Table And Extract Results In A Table

Jul 24, 2008

I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.

View 9 Replies View Related

Custom Sort Of Row Label In Pivot Table

May 3, 2014

Everyday I've to make Pivot Table from Data given by Branch Manager. When I create Pivot, my Row Label comes in order like

SCV shown in "automaticPivot" sheet in attached file. Later I arrange it in order of
SCV by dragging manually everyday shown in "desiredRowLabelSort" sheet.

Is there any way that sort Row Label automatically instead of manually OR any other faster way than doing manually?

View 6 Replies View Related

Formatting Pivot Table With Custom Ranges?

Feb 2, 2014

My boss asked me to format a pivot table like this with irregular ranges:

I've had a crack (as seen below and have gotten everything right except the strange group irregular ranges. I'm stumped! I'm not sure if pivot tables even have a function for irregular group ranges.

My raw data looks like here is its layout:

View 7 Replies View Related

Copyrights 2005-15, All rights reserved