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?
View 4 Replies
ADVERTISEMENT
Aug 12, 2010
In Excel 2010; the pivot Tables drop down filter is limited to 10,000 records. if more than 10,000 are available then a message saying "Not all items showing" is displayed at the bottom of the list.
Clickin on the message would display a window saying: "This field has more than 10,000 items under one or more parent items. Only the first 10,000 items are displayed under each parent item."
View 3 Replies
View Related
Dec 22, 2011
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)
View 4 Replies
View Related
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
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:
Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
[Code]....
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
Next
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
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
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.
View 3 Replies
View Related
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
Code:
Sub Test()
Const StartDate As Date = #1/15/2013#
Const EndDate As Date = #2/13/2013#
[Code]....
View 9 Replies
View Related
Mar 13, 2014
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 PivotCache
Dim VBAAPPC As PivotCache
Dim VBAPRPC As PivotCache
Dim VBAEXPC As PivotCache
Dim VBAMJPC As PivotCache
Dim VBAIVPC As PivotCache
Dim VBAPT As PivotTable
[code]...
View 1 Replies
View Related
Apr 26, 2012
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)
View 3 Replies
View Related
Oct 9, 2008
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?
View 9 Replies
View Related
Jan 4, 2014
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
Excel 2010
View 1 Replies
View Related
May 31, 2013
In Excel 2007 is it possible to filter the "Values" in a Pivot Table?
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.
for example:
Row Labels Sum of Gross Wt. - Lbs
10003150588929
10003153258700
10003155984958
1000315651530
10003156549761
I know I can simply copy and paste into a new tab and sort it there, but I'd like to be able to do it internal to the pivot table if that is possible.
View 3 Replies
View Related
Jan 22, 2007
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.
View 6 Replies
View Related
Jan 17, 2014
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.
View 1 Replies
View Related
Apr 17, 2014
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.
View 9 Replies
View Related
Jun 5, 2014
I have a pivot table like the one below.
What I would like to do is filter the drill down keeping the total of the products (in bold) and showing just one of the name (just ENTA for Example).
Basically I would like to add a filter that Hide some of the data keeping the row total.
I'm Using Excel 2013.
Products
Sell out 4 weeks
Stock Units
Avg 4 weeks
Wks of stock
3160-24PC-AP12
1
[code].....
View 1 Replies
View Related
Apr 28, 2014
I had a workbook in Excel 2003 that i just moved to 2010. In the 2003 file I had 2 pivot tables, one each on a worksheet, looking at the same data, just grouped differently. One yearly, one quarterly.
Now in the 2010 workbook whenever I change the grouping on pivot table, the other one changes also. It's like they are linked together or something.
View 3 Replies
View Related
Sep 13, 2012
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.
View 2 Replies
View Related
Apr 10, 2013
Using Excel 2010
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:
XXS, XS, S, M, L, XL, XXL, MT, LT, XLT, XXLT, 1X, 2X, 3X, 4X, 5X
View 2 Replies
View Related
Nov 26, 2013
I'm trying to create a pivot in vba. I can record what I have done but if I run that recorded code, it returns an error "Invalid call procedure or argument".
Code:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Sheets("Raw Data").Range("A1:AG" & Z), _
TableDestination:=Range("A3"), _
[Code].....
View 1 Replies
View Related
Mar 8, 2014
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.
My data connection is built as follows:
Code:
ThisWorkbook.Connections.Add "ChartQuery", "", "OLEDB;Persist Security Info=0;DSN=Excel Files;
DBQ=" & wbFullName & _ ";DefaultDir=" & wbPath & ";DriverId=1046;MaxBufferSize=1024;PageTimeout=5;
BackgroundQuery:=False", sSQL, 2
(And yes, my sSQL is sound, there are no typos, no special character issues)
The filter in the Pivot Table shows my value (for example, 14) however it always only displays a zero
My Pivot Table is formatting the field (like the others just like it) as a Number, two decimals
My Pivot Table data source is ChartQuery
The worksheet this is pulling from is also set to use Number format for the entire column, but changing that doesn't make any difference even on the other fields that are working.
View 9 Replies
View Related
Apr 1, 2014
Using Excel 2013,
I clicked on a field in my RowLabels
I then clicked on the Filter Arrow for the RowField
The SelectedField prompted with the correct field of 4 possible fields
However the item I am looking for is not in the list but I can plainly see it on the screen.
View 3 Replies
View Related
Jun 28, 2014
How to get Power-query and Power-pivot for Excel 2010?
View 9 Replies
View Related
Apr 25, 2014
I have a Pivot table showing costs of several types of items.
The issue is, the cost of an item is not in U$, it is in amounts of the unit value (example: Unit value of an item (cell A1) = U$500, and Item Cost (cell B1) = 2, the total cost is U$1000).
So instead of showing just the Item Cost (U$500) in the pivot table, I need to show 500*2 (A1*B1) which would be U$1000.
Can this be done in Excel 2010?
I don't want to create a new column with that multiplication, because I'm doing a monthly cost for the items.
View 3 Replies
View Related
Jan 27, 2014
Why do blank columns with no information sometimes appear when I prepare pivot tables? Excel Version 2010. I hide the columns, but many times they re-appear on screen or print-outs.
View 1 Replies
View Related
Dec 1, 2011
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.
View 1 Replies
View Related
Jan 21, 2012
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.
View 9 Replies
View Related
Mar 22, 2012
I have three worksheets Sheet 1, Sheet 2 and Sheet 3 with three Pivot Tables
Sheet1 - Sheet1Pvt1
Sheet2 - Sheet2Pvt2
Sheet3 - Sheet3Pvt3
When I use In Sheet 2 the following lines of code
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Application.EnableEvents = False
Application.ScreenUpdating = False
MsgBox "The pvt table refreshed " & Target.Name
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
and even If I Refresh Sheet 3 pivot table, Sheet 2 Event is triggered and it prints "The pvt table refreshed Sheet2Pvt2";
How to restrict the event code only to Sheet 2 and pvttbl Sheet2Pvt2.
View 3 Replies
View Related
May 23, 2012
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.
View 7 Replies
View Related
Jul 30, 2012
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.
View 9 Replies
View Related
