Sort A Pivot Table Based On A Range
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
ADVERTISEMENT
Oct 6, 2008
I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,
Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath
First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.
View 3 Replies
View Related
Mar 14, 2012
I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.
Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.
In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.
Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...
'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem
[Code]....
View 4 Replies
View Related
Mar 5, 2013
I have the following code to update a pivot table:
Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
View 2 Replies
View Related
Aug 16, 2009
I have a pivot table and if i select my headers i have the availability to sort a-z and z-a plus manual ...manual says to drag the item where i would like it... I have tried the excel database..they reference manual sort but really give no explanation.
Scenario.. I want to sort a-z.. then manual sort a few items to the top of the list..
View 8 Replies
View Related
Jun 4, 2013
I have a table that looks like this:
Code:
TechNum | FirstName | LastName | System | WorkDate | Values
I need the pivot table to look like this:
Code:
-->System
--> TechNum - FirstName LastName
--> WorkDate
I need the sorting to look like this:
Code:
-->System
--> TechNum
--> WorkDate
How do I show the first three fields in the same level of the pivot table and still maintain the sort cascade?
View 1 Replies
View Related
Feb 19, 2009
I need to format some information in an Excel file, before exporting it. All the pivot table information I have found online is primarily for account management, using counts and sums. The data is currently in this format:
DEV#WIRE#SWITCH-1A123AMP-1A124AMP-1A125AMP-1A126SWITCH-2A127
I need to sort it into this format in another sheet or another area in the same sheet:
DEV#WIRE#1WIRE#2WIRE#3AMP-1A124A125A126SWITCH-1A123SWITCH-2A127
View 9 Replies
View Related
May 3, 2014
Everyday I've to make Pivot Table from Data given by Branch Manager. When I create Pivot, my Row Label comes in order like
ICV TRUCK
LCV BUS
LCV TRUCK
MCV BUS
MCV TRUCK
PICKUP &
SCV shown in "automaticPivot" sheet in attached file. Later I arrange it in order of
MCV BUS
LCV BUS
MCV TRUCK
ICV TRUCK
LCV TRUCK
PICKUP
SCV by dragging manually everyday shown in "desiredRowLabelSort" sheet.
Is there any way that sort Row Label automatically instead of manually OR any other faster way than doing manually?
View 6 Replies
View Related
May 14, 2012
I need to put a worksheet together that has multiple columns that I can then sort in order (on any one columns that affects each row together) in "vehicle type".
I want to set it up so the full listing can be sorted top to bottom in colour or then click sort to sort in MAX SIZE and or again sort in SPEED. (So sort in Alpabetical order or by value).
I havent used pivot tables before and assume its the best way. I dont want to use the filter system to sort as its too clumsy for the end user.
sort>sort>sort>sort>VEHICLECOLOURSMAX SIZESPEEDA9brown50100PRONTObrown100110VELICITOgreen200120
SPECIALpurple50155ZEROpink2585GREENVgreen5155MIDEOblack2000135DELVOpurple300155ASPIROblack6155
View 2 Replies
View Related
Jul 23, 2012
I have entered a custom sort order under 'first key sort order,' but the field is not sorting according to the list.
I'm sure I entered the list correctly, because I actually entered it a long time ago, and it was working for a while. However, today the underlying data was temporarily erased, and the pivot table, when refreshed, basically went blank. I restored the data and refreshed the table, but this field is back to sorting in the wrong order. Refreshing the pivot table and re-choosing the appropriate sort options do not work.
View 1 Replies
View Related
Feb 15, 2008
Is there a way to generically sort all pivot rows of a pivot table alphabetically each time the table is refreshed? I have a pivot table that pulls its data from a dynamic named range on a separate worksheet. As users add data to the range and refresh the pivot table the newly added items appear at the bottom of the table rather than alphabetically. I know how to specifically call out a pivot row and have it sort alphabetically (see code below) but I still want to allow users the flexibility of changing the fields and order of the pivot rows so specifically calling out a pivot row by field name isn’t going to work. I need to somehow add code that says: For each pivot row (1 to n, where n = number of pivot rows) sort each in alphabetical order.
ActiveSheet.PivotTables("ptDWDM").PivotFields("Project Name").AutoSort _
xlAscending, "Project Name"
So in the above code I don’t want the reference to “Project Name” to be hard coded.
View 2 Replies
View Related
Aug 16, 2013
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
View 3 Replies
View Related
Sep 5, 2006
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
View 3 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
Jan 9, 2014
From what I understand there's no way to do this without using macros. I would perfer not to use macros, but I need my PivotTables to auto-refresh anyways and apparently that will require a macro, so oh well. I'm very new to macro coding so I can't seem to successful apply any of the previous threads about this to my sheet.
I just need my pivot table on sheet "Customer" to filter the customer field based on the value in cell C1. Also I need to make sure the sheet doesn't "freak out" if the value in C1 is either blank, or is a customer value which doesn't exist in the table. Preferably in this scenario it would display nothing on the pivot table, but I don't know if that's possible. I want the sheet/macro to allow an invalid filter value in C1 just so the sheet doesn't lock up until it is corrected.
I also need my workbook to auto-refresh all the pivot tables is that's easy to code in as well.
View 2 Replies
View Related
Jan 27, 2014
filter my pivot table using a cell value, rather than manually selecting from the dropdown.
The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).
Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?
View 5 Replies
View Related
Mar 11, 2014
I try filter a pivot table based a cell.
[Code] .....
Attached File : FilterPT.xlsx
View 3 Replies
View Related
Jan 16, 2009
I do not know if this is possible, I have a pivot table, however I would like to be able to sum a particular range based on start and end date. then by make and model as the second set of criteria, The sum would be displayed into a form on a different worksheet. attached is a file so I would like to know the total
View 3 Replies
View Related
May 16, 2013
I have a data set that I constantly run a pivot table. I have the pivot table run on a separate sheet each time. Since I run it every time I was wondering if there is a way to set up a pivot table to run automatically and run it on a separate sheet.
Lastly, if that is possible, if the data set changes and the pivot table runs automatically will the pivot table recalculate based on the new data.
View 9 Replies
View Related
May 6, 2014
I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
View 1 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
