How to hide the Pivot Table pagefields . Because I have so many filters on my pivot table to get it where I want it to be, I chose to use some PageFields. Trouble with these, is they look awful on my report which will be issued in PDF format so not an interactive field at all.
When I use the PF.Orientation =xlHidden on a page field, it affects the accuracy of the report
I have two pivot tables on one sheet and I want the page fields on the second to change when I change the first pivot table. I found the below code and have applied it to one of the three page fields I have, but can't seem to duplicate it for the other page fields:
Im using the following VBA code, but the on error resume does nothing, excel throws out the error 1004 "Unable to get the pivotfields property of the pivottable class" (i know why the error is caused and how to fix it but my error trapping wont work). Is there some option which i have turned off which prevents me from trapping errors?
Dim ws As Worksheet Set ws = Excel.Sheets("Sheet1") On Error Goto err: If ws. Range("IV1").Value = "dog" Then ws.PivotTables("PivotTable1").PivotFields("Price Euros").Orientation = xlHidden Else ws.PivotTables("PivotTable1").PivotFields("Price Dollars").Orientation = xlHidden End If
I have pivot tables with one row field and two or three data fields. I want to be able to quickly hide rows that do not have entries in *all* data fields.
I thought of auto- filtering my data source, but don't think that would be practical as I want to have several tables powering off of the same source data (with many columns), and also the data source is auto-filled with API... Is there a way to traverse the rowfields with vba and hide them if there isn't an entry for every data field? Or a better way than both of those?
I was wondering if it is possible at all to hide or delete the default page field option to select "(All)" in a pivot table, and to just have the pivot default to the first list member in that page field.
I have a workbook with three sheets. The first is a list of employees with their managers. The second is the data about those employees (who they sold product to, and how much, on what date). The third is the PivotTable with the employees' sales. I have the Customer name in the Row field, and the amount in the Data field. I have two page fields - the Manager's name and the Employee's name. The example that I'm using so as not to violate my NDA is a Northwind knockoff. That's why the names probably sound familiar.
What I want to do is have the second pivottable page field (Employee) change, so that when I choose a manager's name, only the employees who report to that manager show up in that page field. I have a named range called "allEmp" (all employees), one called "buchanan" (all the employees who report to Steven Buchanan), one called "fuller" (all the employees who report to Andrew Fuller). (File attached, btw).
Private Sub Worksheet_Calculate() Dim mgrRange As Range mgrRange = Range("allEmp") Range("$b$1").Select Select Case ActiveCell.Value Case "Andrew Fuller" ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("fuller") Case "Steven Buchanan" ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("buchanan") Case "Susan Erickson"................
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;
Sub RemoveGhostPivotItems() Dim ghost As PivotItem Dim pt As PivotTable Set pt = ActiveSheet.PivotTables(1) pt.ManualUpdate = True For Count = 1 To 10 On Error Resume Next For Each ghost In pt.PivotFields(Count).PivotItems ghost.Delete Next ghost Next Count pt.ManualUpdate = False End Sub
My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............
I have a pivot which under a Main product, there are a few sub products. I have act, budget, forecast etc figures but mainly want to only show the Total Act and not the rest, see the highlighted brown columns which I do not want to show in the pivot.
Then I want to group the clients, salesperson together so that the details can be hidden.
This is a small example. I have a large pivot table with lots of teams and salespeople and clients and a few main products with a few sub products within it. Grouping it manually is going to be very difficult and would appreciate if there is some way to automate it either via pivot capability or VBA.
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X) end if"
it's possible to create new fields (variables/columns) on the fly (without adding them to the original data table).
For this example, assume I have a column for: job code estimated hours job code actual hours total estimated hours total actual hours
Excel has made it easy to run averages or sums for any of those columns, but what if I want to see what the average proportion for job code estimated hours/ total estimated hours? Is there an easy way to do it with pivot tables without adding a column to my database? I'm working in Excel 2007, my data table size is approximately 100 columns by 200 rows.
I am finishing up a macro that manipulates a pivot table and creates a file for us to run. But we all keep this pivot table up a lot, and usually keep a lot of the data filtererd in it.
Is there a way to set all of fields in a pivot table back to their default values like when you first open up the workbook?
I have a question regarding pivot tables. how do I hide a field but still maintain that filter? I have a field in the Column Fields titled "International". Right now I've filtered out only the YES entries, but I don't want to see a "YES" column beside each. When I try to hide the column the filter is reset and all entries, international or not, are seen. Can I hide the column but maintain its filter?
What is the easiest method to eliminate non-corresponding items from appearing in pivot fileds. For example, if I select "Texas" and "Arizona" from my state pivotfield, the "City" pivotfield next to it should only display cities within these states, and not every city in every state within the original data.
I presume this involves dynamic ranges and also some sort of array matching?
My Pivot Table data section contains calculated fields and normal fields...both have sub and grand totals. The calculated items are correct but. the calculated field totals are incorrect. The normal field totals are correct. The calculated field totals are ... the True total multiplied by the number of items!
I created a nice excel file with some cohort analysis regarding the number of months customers of different cohorts stay with a company. Basically I do a little manipulation on the raw sign-up data sheet and then run a pivot on it.
I want to see the cohorts in two ways: 1. number of customers. 2. $ from customers.
I have the first 1 and I want to get the second one without going through the same procedure again (i.e. manipulating the raw data). In order to get the $ terms, all I need is to multiply a $ column in each row in the columns that indicate the number of months that passed since a customer joined (see the attached example file please). When I try to do it with calculated fields in the pivot table, Excel multiplies the total number of customers with total $ from them all put together, which is of course wrong.
Any way to achieve what I want without having to created another data sheet just for $ terms?
I believe the attached excel file: dummy_for_cohort.xlsx
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
My data has a field for "Donor Type." There are about 2 dozen types of and about half of them start with the letters CF. When I do a pivot table, instead of getting 2 dozen rows of data I'd like all the CF donors to be in the same row. Is there a way to do that without changing my data? Some sort of filter or grouping within the pivot table?
I have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.
Day Count of Posts Sum of Impressions Impressions Per Post
Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.
The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..
The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.
I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
I have pivot tables in diff pages im trying to make a button to reset all the fields in all the pivot tables..
i tried recording a macro to do this but it only works for page fields.. it wont work for the column fields.. act, adopt and check are page fields at the top... region is a column field ... goto_AQcheck is a macro to switch to that sheet which is assigned to another button....
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date ------------------------------------------------------------- John , 100 , $150 , 2011-04-01 17:07:50.0 John , 100 , $250 , 2011-04-01 17:07:50.0 Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase ----------------------------------------------------------- John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
I have a pivot table listing different company names in the first column under 'row labels' and there are calculated fields, a count and an average in columns B and C respectively. Is it possible to insert another field in column D that calculates the difference between values in column B and C (even though they are calculated fields themselves)?
