Pivot Table - Filtering Against Specific Range
Jun 20, 2012
I am trying to create a Pivot Table that filters on a range of data.
I have a sheet with lots of data on it, and separate sheet where I have created my Pivot Table and my list I wish to use as the filter values.
I need to extract 630 specific Departments out of thousands and report the movements against those depts. The 630 will change as time goes on and I want to just have a range where I can update the values as and when changes occur. I initially tried using Filter on Department but realized this was going to be a very long winded process.
View 4 Replies
ADVERTISEMENT
Jul 19, 2013
Is there is some way to filter based on the value in a specific subcolumn.
Using the example of a list of salespeople and their transactions over the year, who sell multiple products, the PT is Sales Person name for the Row Labels, and Type of Product for the columns. The resulting PT has 3 columns, for each of the products - e.g. table, chair couch, and the Values are the total number of that item sold. Is there any way I can filter, so that I will see only those sales people who have sold 3 tables or more lets say. (Thus enabling me to quickly see what other products those sales people have sold.)
(The actual situation is a lot more data heavy than that - it is actually a list of donations for a non-profit, coming in from hundreds of people, across a dozen different categories; I am trying to analyse the extent to which people who gave for a particular category (Direct Mail solicitation) also gave across other categories.
So far I've manipulated things by inserting a '% of row total' value into the PT and then using countif/sumif functions outside of the PT to figure out how many people donated solely to this category (= 100% of row total), and how many donated to other categories too (= more than 0% of row total, less than 100%), which gives me a decent summary of sole donations to this cause vs other categories too, but doesn't visualise what the actual other categories donated to were. I've also investigated making a PT of the existing PT, but I'm not sure if that's even possible...)
(I'm using Excel 2011 for Mac, but if there's some other version that would make this possible, I may be able to use another computer.)
View 1 Replies
View Related
Jan 20, 2014
I am using a pivot table in excel 2010. 15 columns (fixed) and plenty of dive downs for rows.
I am trying to 'hide' all of the rows containing values less than 10. If it is easier we can start with hiding values of zero and go from there.
It is key to hide the rows as within the dive downs the rows within the pivot are to many to sort through. If there is a way to do this using the grand total columns for each row that would work as well. Just to be clear I need to hide the entire row not just report the empty cells as zeros. If any further detail is needed feel free to ask.
For example: If value of column O "Grand Total" = 0 'hide rows with value 0' ELSE 'display rows with value greater than 0'
View 4 Replies
View Related
Jun 2, 2014
I have a Pivot Table (pivottable1) starting from cell A1 (lets asume that this cell is "Pick up date"). What I want to do is to filter by each "Pick up date" one by one - for example by first date 2014-05-30 than do some stuff (I have this part of code so no worries) and after that pass to next Pick up date do the same stuff pass to the third pick up date and etc. The problem is that the number of Pick up dates can be different - sometimes it could be 10 pick up dates and another time it could be 150 of pick up dates. I suppose it should be some kind of loop but I have no idea how to start this.
View 2 Replies
View Related
May 12, 2014
how can i sum pivot table filtering some values in other table. if i change filter, sum is changed
View 1 Replies
View Related
Nov 27, 2011
I have a pivot table with a group # on the left, and team members on the right. There are multiple team members in each group, and each are on a separate line in the pivot table, like this:
Group #Team1chrisdawnsally2cassiechrisdawnkathysally3jimjoejohntomGrand Total
I need to find where "sally" is on the team (easy to do"), BUT I need to display the rest of the team members in the group with her, like this:
Group #Team1Sally, Chris, Dawn2Cassie, Kathy, Sally, Dawn, ChrisGrand Total
Using the concatanate function to put them all into one cell and then filtering for "contains" was all I could come up with, but there are 14,000 groups
View 2 Replies
View Related
Oct 2, 2012
I am currently using the following code below in the macros for filtering out a certain set of activities in fields LVL to be "0"
VB:
Dim PI As PivotItem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Lvl")
.PivotItems("0").Visible = True
[Code]....
I now wish to filter lvl activities with pivot items " 0" and "1" as well.
View 5 Replies
View Related
Jan 18, 2012
I'm working on pivot tables using excel macro. Basically , I need to filter out the year submitted according to the current year . I did this to filter out my pivot :
For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
PivItem.Visible = True
Next PivItem
For Each PivItem In _
PT.PivotFields("Year Submitted").PivotItems
Select Case PivItem.Name
Case "2012"
[code]....
But this can only filter out year 2012 . I need to use this workbook for the next couple of years and I dont want to keep modifying the codes .
View 6 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
Sep 12, 2013
right now, formula is simple as follows
Code:
Sheets("Shift Premium").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("DT_REPORT_DATE"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields("DT_REPORT_DATE"). _
CurrentPage = myDate
The idea is that the pivot table always displays yesterdays data.
However, there may not be any data for that day, so when i tell the macro to filter for it, I get an error.
My question is, how could I formulate some sort of iferror statement so that if yesterdays date isnt in the filter list, then it does nothing (by default leaving the filter on last populated date)?
View 2 Replies
View Related
Sep 10, 2013
Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.
My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.
View 2 Replies
View Related
May 14, 2013
I am working on a pivot table just like the one on the picture here [URL] .......
Ideally I wanted the sums by date to be displayed horizontally and not vertically as shown in the picture, but was unable to do so. Anyways, I want to be able to filter those dates, so I can display data just from an specific date, and then change this date whenever I want and the new data will show up.
I tried doing by the checkbox that appears under the "data" dropdown on top of the column; However, when I uncheck a specific date, it dissapears and I have to add it again if I want to see it. Is there a way to keep the values on the dropdown even after I uncheck them? Or even another way to filter the information? Below is the pic of the dropdown I get when trying to sort the data, But as I said If I uncheck one of the values, it dissapears from the list.
[URL] ........
View 5 Replies
View Related
Aug 19, 2012
is there any way to NOT Displaying Items with No Data when filtering data in my pivot table?
For instance, I have 2 report filters: Category and Subcategory, when I select a category in the first filter I want to see only the options of subcategories with data in the second filter, I mean display only the subcategories of the Category previously filter.
Same scenario I have with a report with Directors and Organizations, when I filter one Director it would be nice to see only the organizations of this director and not all options on the data.
View 1 Replies
View Related
Jun 4, 2014
See attached.
Basically, I just want to do a pivot which shows the Name and Number for those names listed in the LIST tab.
The pivot source is the data tab, and the result is in the result tab.
I have the code to create the pivot, but filtering it for those specific names on the LIST tab is where I am getting stuck.
Attached File : Excel VBA Pivot Problem.xlsx
View 1 Replies
View Related
Jan 18, 2010
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.
View 9 Replies
View Related
Jun 20, 2014
I am trying to Auto filter on a pivot table "dates" via VBA coding.
Where:
Startdaycomp = 11-May
Finishdaycomp = 18-May
Rather then this showing all values between these dates, I was hoping to see only those two dates. This will enable my to compare weekdays for a Dashboard.
Is this possible?
View 7 Replies
View Related
Sep 5, 2013
I've attached some dummy data. Basically, everyday I'm going to dump a report containing data into a 'Data Dump' tab. 'Pivot 1 - Filtered' I have set up so that it will show specific product IDs only (In this example I only want to see 1X, 2X and 9D).
However, I now have a need to see the total value of that order number in 'Pivot 2 - Autofilter'. (For Example, order number 1111 has both 1X and 8D contained within it). Now obviously the simple thing to do would be to filter every order number shown in pivot 1, and filter pivot 2 with them. However when I'm doing this for real, this can be tens/hundreds of order numbers.
The only workaround I have found is to put a column at the side using VLOOKUP and if ISERROR is false then include in pivot, then used an autofilter on that, but I'd like to keep the normal pivot if possible.
View 1 Replies
View Related
Oct 28, 2009
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
View 3 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 29, 2014
I have a pivot table summarizing a lot of weight data on the product that we make. I have the actual weight and the specification weight, and a calculated field for the % over or under weight.
What I want is also a pivot column to show the range of weights for each product code. I have added a column (calculated) giving the weight difference (in gm) but when I collapse to summarise for each product it gives the sum of the differences. I think that the best thing may be to use an array formula on the source data table in order to give the range but I have not tried that yet.
View 3 Replies
View Related
Dec 27, 2013
I have a data management issue that I have been able to resolve using SUMIFs and a custom table, but not in pivot form. Since the data source is a SQL database, I would prefer to resolve the issue in pivot form so that future values chart correctly. The data is formatted as so:
Job
Day Start
Day End
Size
Project
1
1/1/2014
1/10/2014
10
A
[Code] ......
From that data I want to see the total size of projects active at the start of the week for the past year. This would essentially mean summing the size of the projects if the week start date lies between the day start and end. This is what I ended up with:
Year
Month
Week
Weekday Start
Active Size
2014
1
1
1/1/14
10
[Code] .....
This is the associated pivot chart:
This would be "OK" except that I would also like to see the size breakdown by project, like this:
This is where I would need the data to be handled purely in a pivot so that when a new project is added, the pivot table will automatically make the summing column for it rather than my manual creation of columns for the data.
I have uploaded a link to a work book with the examples of the data and how I made the charts above. You can see that to make the second chart I had to manually create the columns for each project:
Download PivotExample.xlsx from Sendspace.com - send big files the easy way.
View 3 Replies
View Related
Jun 17, 2014
I was using the following code for finding the max value in a range from a Pivot Table but cannot manage to make it work...
[Code] ..........
View 1 Replies
View Related
Nov 8, 2011
I've a macro that generates a pivot table from another excel workbook, however i need the range to be dynamic as the excel workbook im generating the pivot table from changes in number of rows on a weekly basis. Here is the existing coding:
Code:
Sheets("QA").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"QA!R1C1:R22580C12", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10
how to make the SourceData dynamic
View 1 Replies
View Related
Mar 27, 2013
I have a sheet in my workbook called Results. This sheet contains a list of a fixed set of columns (A - N) but with a variable number of rows.
I am trying to create a macro to create a pivot table from this data, which automatically selects all the rows in the Results sheet to form the data set.
I've had a look at some of the other threads on this topic, but just can't get this working.
I have the following code that I have pieced together from other threads - you will see I am trying to set "PivotRange" as the data set and include this as the data source in the pivot. I get a subcript out of range error when I run this.
Dim PivotRange As Range
PivotRange = Range(Worksheets(Results).Cells(1, 1), Cells(lastRow, lastColumn))
Sheets.Add
[Code].....
View 1 Replies
View Related
Aug 30, 2013
I want to start copying at a cell that contains the word "Vehicle" that is in the middle of a pivot table and copy until the end of the pivot table but I don't want to copy anything before it. How can I word this to in VBA?
View 1 Replies
View Related
Jan 6, 2009
Because my pivot table is used by several users the data source changes often. In response, I created and applied a dynamic range using the offset formula. While the pivot talbe grabs the correct # of records, it inserts a blank column and row. There are NOT any blank cells in the data source range, as reflected by pivot table counts for the Blank column and Row. Any idea why Blank is showing up in my pivot table and how I can correct while maintaining the dynamic range?
View 9 Replies
View Related
Dec 21, 2007
Im trying for the first time to use a dynamic range as the 'source' for a pivot table. The pivot table and the source data are in two different tabs. The source data is on tab Log! and i have used Daves formula (i hope correctly) to define the dynamic range. The range starts at Column B and is 22 columns wide:
Refers to:=OFFSET(Log!$B$1,0,0,MATCH(""*"",Log!$B:$B,-1),22)
I have named this dynamic range DMR. I have a pivot table on another tab. When i try to use DMR as the source (Log!DMR), it gives me an error "Reference is Not Valid". I must have the syntax wrong somewhere.
View 3 Replies
View Related
Apr 19, 2013
I prepared a Pivot Table and then i add some data in it but now all data is not showed by Pivot Table.
I do all necessary things like refresh and all but unable to increase a range.
How to increase range for Pivot Table in 2007.
View 3 Replies
View Related
Nov 12, 2009
I wonder if someone can tell me if it is possible to sort rows in a pivot table based on a range in the same workbook.
I have the pivot table set up as I need it but the values in the row change each time the macro is run (it is run when new data is added)
As part of the macro a list of Grades is imported into the Workbook. These grades are always imported in the correct order. As such I would like the pivot table to follow the order of imported grade list. The grades in the list will always be the same as the list in the pivot table.
Is this something that would be possible to do?
View 8 Replies
View Related
Jan 30, 2012
I have created Pivot Table in "Sheet2" and the source data is in "Report" tab. The source data is in the range of A1:K200 -means until the last cell of the excel. Now if the data is more then this we need to change the source data every time before we refresh the pivot table - Is there any macro or any way so the range can be change automatically ?
View 9 Replies
View Related
