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?
Is there a way to have separate Report Filters for each column in a pivot table?
I am trying to create a pivot table with 3 columns - 1: account code 2.current year (2011) 3. prior year (2010)
My Row Label is an account code field - each row is a different account code that sums up dollar amounts for the two financial years. The problem is that I need Columns 2 and 3 to have separate Report Filter criteria. So in Column 2 - I need the filter to weed out anything that is not 'Financial Year 2011'. In Column 3, I need the filter to weed out anything that is not 'Financial Year 2010'. Setting up a filter for one screws up the second, and vice versa.
Alternatively, how can I make two side-side-pivot tables that each have their own Report Filters?
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?
I'm trying to update a pivot table filter with a list on another worksheet. When I do a record and select two WBS for filter criteria I get the following:
If I entered values 700UY0S1MGL1 & 700UY0S1MGL2 in cells A1 and A2 of Sheet2 in the same workbook, how can I get this code to read that? If I extend the list to include additional items, how can I get the code to read it?
My next question is, in Excel I could enter a partial search (like 700UY0S1MG) in the filter drop down and I could filter on everything with this string. Using the macro it seems as though the full 12 char string needs to be qualified. How would I write a macro to handle this?
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!)
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.
filter a pivot table that contains these 3 column fields: User 1, User 2, and User 3.
However, when I want to see the results for "John" in all those fields, I need to use one filter at a time. Is there any other way to see all results of the Pivot Table where "John" is a User, no matter if he is the 1, 2 or 3 user?
I have A3 and A4 as report filters in the pivot table. Is there any way can combine A3 and A4 into one cell. The new cell is something like drop-down list contains contents from A3 and A4. When picking up the from this drop-down, the corresponding data list will be shown (like the data in B3 and B4).
I can't know what fields will be filtered on so, so I need to extract all filters at run time.
I tried activeCell.PivotCell.PivotTable.ActiveFilters and this returns 0 although there are definitely filters available.
The workaround which I'm not is very good I'm considering is:
activecell.PivotTable.PageFields.Items collection which also behaves weird:
activecell.PivotTable.PageFields.Count returns 2 at the same time it has 3 elements: activecell.PivotTable.Item(0);activecell.PivotTable.Item(1);activecell.PivotTable.Item(2)
I have a pair of Pivot Tables with matching Pie Charts that look at Project Costs and Schedules. When I filter one, I'd like the other to filter automatically.
I found these links and used the code that Jerry supplied but I am running into trouble.
pivot table question
Pivot Table- "Master" Report Filter My filters (PivotField names) include Contractor RCEs District Year Month
When I select a contractor from PivotTable1 it does not change PivotTable2.
There is additional code in this workbook that Jerry recently wrote concerning drilldown formatting. It was initially created for a different report but now I use it in many of my other reports. I don't think it's causing the problem, I placed the new code above it (including in the module) and there is a line breaking it up from the new code.
As a side note, I may or may not need to change every filter. In this case I only want to change the contractor, but other times I may want to change other items.
Is there a way to loop through pivot table filters in VBA? In my case, I need to loop through the filter "Month" which contains January - December. For each of the month, I have to select and double click the total cases in each month.
So for every loop, for example,
Pivot 1 January Count
Case 1 2
Case 2 1
Total 3
I need to open the drill down in B3 (this drill down will be opened in a new sheet, Sheet 1), copy the data and paste it in another sheet named "January Cases". Then the code will delete "Sheet 1" and proceed with the same with February till December. Is it possible to loop through pivot table filters?
PIVOT TABLES -- Old data remains in pivot table filters
One more Pivot Table Question:
I have workbooks i have built and re-use with fresh data after verifying that the previous data has been cleared. Unfortunately there are times in the drop down filter, old data will remain, but is not at all in the data set. How to clear this out? I have ensured that before pasting in new data the old data has been cleared below the headers. I keep my headers so I don't need to re-do the Pivot Tables or adjust the Name / SUM capabilities in the VALUE FIELD SETTINGS.
I had 2 pivot tables running on the same page (sheet 2). They both draw data from the same source (Sheet 1).
The only difference between the two is that pivottable1 has a filter for "home team", and pivottable2 has a filter for "awayteam".
I have managed to link the tables using VB so if i select "Man United" on pivottable1 - as the home team, pivottable2 also selects "Man United".
Now my problem is, that i don;t want to select the teams from the drop down check box list on the actual pivot table. I want to be able to do this by typing into a cell (say Cell A1 on sheet 3 for instance) and this controlling BOTH pivot tables.
I did have some lengthy code which worked for one pivot table, but it did not operate both and it over rode my code i had in place which works for manual filtering.
I'm trying to make a pivot table that can compare sales based on the whatever month/year/salesman combination I give.
Ideally, I'll be comparing the sales data for 3 seperate months. Can I make a pivot table where I can make different filters apply to specific columns?
I have a pivot table with over 4000 entries and about 12 columns which i filter to get the info i want.
Unfortunately with so many rows and columns in the table, if i do a macro that gets each filter back to 'show all' its really slow.
Doing this manually using the "Show all" option in the filter takes a fraction of a second, so surely there must be a way to do this more quickly in VBA?
i can't understand why manually it takes no time at all, but automating it through recording a macro takes forever.
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
I'm Using Excel 2007 and would like to have some VBA to work with the following!
I have a simple pivot table (PivotTable1) in Sheet1 with three items in the Report Filter which has been named "ROUTE" I have created a ComboBox in Sheet2 and have added the identical three items in via format control, cell link A1.
I would like to be able to use the combobox in sheet2 to operate the PivotTable Report Filter in Sheet1 as I would like to build a report whereas a user. Can only select the comboBox and does not see the pivotTable
Sounds simple but cannot get this to work no matter what I try.
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".
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.
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.
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)
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")
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.
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.
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.
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
