Excel 2010 :: Pivot Table Filter Query

Feb 21, 2012

I have created a pivot table in Excel 2010 that lists amounts used of particular items each month. Inside the pivot table value field settings i have changed the show values as field so that it displays the difference in usage from this month to last.

However if i now select a top 10 filter it only filters by the total number used as opposed to the difference in usage from one month to the next (which is the values displayed).

Is there a way that i can filter by top 10 by the actual values displayed in the pivot chart and not just the underlying data that creates it?

In the coming months the company i work for will be transferring from Excel 2003 to Excel 2010 (i know, a little late......) and now i am testing some things at home.

We deliver lots of Excel reports to our clients where we are using Pivot tables. In excel 2003 we where able to hide items from the dropdowns using properties - hide items but when i now open an excel 2003 file in excel 2010 and want to filter the pivot table to (let's say) another month i see all my hidden items.

Is there an option in excel 2010 to hide items like i could do in 2003? (either regular option or VBA)

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.

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.

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:

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.

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.

When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.

This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.

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

My macro is designed to look at a summary source tab and create a new tab for each unique project number. It then creates a pivot table from five different source detail tabs and filters on the project number. If a tab already exists it selects the tab and moves on to the next project number. There are six pivot tables created for every project.

New data is added each month to the source tabs and I have a macro to delete all pivot tables and the macro will recreate the pivot tables when ran again.

Issue: Running out of resources At work I'm limited to the use of Excel 2010 (32bit) so I'm restricted on 2GB of memory. At home I ran the file successfully (64bit) and it was around 3GB of memory.

My macro creates a new pivot cache for every pivot table where as I'm trying to only use 6 pivot caches in my coding. I kill it half way through and it's around 100+ caches causing unnecessary usage of memory.

Fix / Solution:

Correctly code the vba to only create six caches and code the rest the pivot tables to use that cache.The only difference in the Pivot Tables is that it’s sorted on the Project Number.

Code:

Dim VBAPPPC As PivotCacheDim VBAAPPC As PivotCacheDim VBAPRPC As PivotCacheDim VBAEXPC As PivotCacheDim VBAMJPC As PivotCacheDim VBAIVPC As PivotCacheDim VBAPT As PivotTable

Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.

Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)

I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value?

I have a table that contains all my data and would like to filter it by company into their own tables that will stay up to date with the main table and then hopefully somehow calculate their investment returns (future stage). I thought Microsoft Query would work but I came across a problem see my other post MS Query returning data to excel some columns I cant sum looks like text

I am tracking the Gross weight of shipping containers. My table sums the weight of all items in a container by container number. So my rows are 9 digit container numbers and my data values is a Sum of part weights. I want to be able to filter out containers above a certain weight.

attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.

I have a pivot table which has a report filter. The report filter can have anywhere from 20 to 350 values. The user will be able to select multiple values from the listing to produce the table as needed. I would like to be able to display what values were selected in the report filter so that the user can see this information once the pivot table is rendered. Is there any way to display this information either above below or along side of the table itself?

I am using Excel 2011 for Mac, but I also have access to Excel 2011 for Windows. Either way will work. I have been able to get quite a bit of what works on Windows to work with the Mac.

I'm on Mac using Excel 2011. This means I don't have "slicers", which is all my Googling kept turning up.

I have a pivot table with 4 different value columns, and I want to be able to filter it the same way you would a normal table - i.e. remove everything below a specific number in one column, and filter for only specific strings in another column, etc.

I have a pivot table in 2010- is there a way to filter the data using an external reference from the pivot table? I'd like to put the value in another cell and have the pivot update automatically when I type a new value in that cell.

I have a workbook with multiple sheets.Sheet1 is named "UPC" and is a giant database for my workbook containing 80,000+ rows and 12 columns.

Sheet2 is named "Scan" and uses a bunch of index-match formulas. Basically, you scan a barcode and it auto-populates across the row all the information it pulls with that particular UPC from the "UPC" sheet. One of my columns, "QTY Scanned", in the "UPC" sheet has a countif formula to keep track of how many times that item was scanned on the "Scan" sheet.

Sheet3 is named "PSlip" and has a big button on it that you press and multiple macros go into overdrive. First macro takes all the rows from "UPC" with a quantity greater than 0 in the "QTY Scanned" column and copies it to the "PSlip" sheet.

Second macro takes that data and makes a pivot table.This is all clothing, pants, shirts, jackets, etc...When my pivot table is created, my Column Label is Size. When the pivot table is created, it automatically sorts it in ascending order, so it goes from numbers to letters.

This is great for pants because the size is in reference to a waist size; 24,25,26,27, etc...

HOWEVER, shirts, jackets, and other tops are not in a numeric size: they are in a Text format. XS, S, M, L, etc...

Sometimes we only scan pants for an order, sometimes only tops, sometimes both.

Is there a way for the pivot table to recognize when tops are in included and automatically sort the sizes?

I still want the numeric values at the beginning, but once the text starts it automatically sorts in this order:

My problem is this: I use Excel 2010 and have Pivot Tables that are using a Data Connection which is being built (and rebuilt) with VBA. The Pivot Table field is calculating a field that contains an integer value. The Pivot Table field filter properly displays this value.

However, the Pivot Table itself calculated Sum field only displays 0 (aka zero). Other fields that are exactly the same as this one are displaying properly.

I tried multiple variations of searches for this, but can't seem to get this to work. I've been asked to perform some analysis on the number of direct reports for each manager in our company as well as the number of total reports. I have been able to calculate this with no problem. My difficulty comes with the request to be able to display this information in a particular way with Excel. (this is using Excel 2010)

What the VP would like to be able to do is to drill down on each manager to see all of their direct reports, and then, for any of those direct reports that are managers, be able to further drill down into those people's direct reports. The difficulty I am coming to is that, depending on the employee, there can be as many as 9 layers of management when I work this all the way up to the Executive Vice President Level.

My data is currently laid out such that my first 5 columns are identifiers such as employee id, employee name, job title and organization information. After that I have columns for the managerial hierarchy. For example first column of this (let's call it column H) is the EVP, the 2nd column (column I) is the Senior VP, 3rd column (column J) has the various reports to the senior VP, 4th column (column K) is the managers that report to those in column J, and so on, up to 9 layers.

For example, one of the employees in my finance area would have name of the EVP in column H, the SVP in column I, the director in column J, and their manager in column L. The remaining columns that could house hierarchy data are blank for them because of where they are in the hierarchy.

When I set up the pivot table, I placed each field of the managerial hierarchy in the row area and then the employee field at the end. The problem I encounter is that with all of the blank cells in the data due to the varying levels of the hierarchy, I get a lot of (blank) fields in the pivot table for any layer below where the person fits in (ie, for the example above, the EVP, SVP, and manager name come in find, but then it is (blank) on down to the employee name.) If I deselect (blank) in the field settings, it eliminates all of the rows that don;t go as far down in the hierarchy, i.e. I deselect (blank) at level 5, any employee that is 4 or less levels from the EVP does not appear.

I also tried doing the same as above but including the employee name in my hierarchy (so for the example above, the employee name would be placed in column M). This worked a bit, but then after every employee was the series of (blank) drill down options.

The ideal solution that I've been asked to come up with would be to have the data display as it does when I've set the pivot table up with the employee name in the appropriate hierarchy level, but not have the drill down (blank) show for those that don't have layers below them.

I get a "Object variable not set" error when creating a Pivot Table from a cache. I want the destination to be in a different sheet in the workbook. I set the new worksheet as: Set WSD = Worksheets.Add and I reference WSD.Cells(2,FinalCol +2) in the destination field of the CreatePivotTable method. I use the code from the Excel 2010 VBA book I got last week.

I have a simple pivot table that is types by state. I want to know how many types are in each state. In the attached image the answer for Alaska is 5, for Arizona 5, for Arkansas 1, etc. How do I get Excel to tell me that for each state?

I'm using Excel 2010. I have PowerPivot installed but really don't know how to use it yet.

Can Pivot Table chart add another data from another sheet? I attached the link for this file (Add data1), it is because the file consists of several sheets and I do not know how to show here.

I would like to add the data from the "Rate" sheet into the Pivot Table chart (Chart.PT). I made an example by using normal way (Chart.Case (9)), the column series in the chart is the one I added from the "Rate" sheet. I wonder if I could do the same in pivot table chart.