I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.
View 1 RepliesOn 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.
I have a problem in expanding/collapsing fields in pivot table.
I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).
The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?
I would like to use some VB code to search a Pivot Table, find a specific value and show the details relating to the value. I used the following code I have obtained from another thread in the forum
Sub Expand_Pivot_Values()
Dim rngFind As Range
Dim rngPTData As Range, rngPTC1 As Range
Dim rngF As Range, rngP As Range
Dim strFind As String
Set rngFind = ActiveSheet.Range("J3:J6")
'set the pivot table data range - see help file for property description
Set rngPTData = ActiveSheet.PivotTables(1).DataBodyRange
'set start cell for find
Set rngPTC1 = rngPTData.Cells(1)
For Each rngF In rngFind.Cells
'set variable to find........................
This code works fine but only identifies the first time the value appears in the pivot table and shows detail relating to that value. I need add to the code to search the pivot table and find recurring values. For example if the value is "2" I would like to show detail on each "2" value there is in the pivot table.
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窶冱 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]...
I have a seemingly very simple question but even though I've worked a lot with pivots I can't find the answer.
clientcode Amount countries
a1 1.000,00 kenia
a2 2.000,00 kenia
b3 1.000,00 kenia
b4 3.000,00 kenia
b5 2.000,00 kenia
c5 1.000,00 senegal
c6 3.500,00 senegal
c7 4.000,00 senegal
c8 5.000,00 senegal
Lets say I have a list like this and I want to count the number of clients (3) or countries(2).
I can only get the total of rows per client but not the subtotal 3 for the number of clients.
a - 2
b 3
c - 4
I can't find a way to display "just" subtotals which are > 500k ..
View 9 Replies View RelatedRecently, 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)
With the following data Excel's pivottable's subtotal shows underlying MAX total instead of subtotals relating to values displayed.
SalesRepOrderNoItemValue
ACON12334
ACON12322
ACON12412
ACON12424
ACON1248
Pivottable:
Sum of ItemValue
SalesRepOrderNoTotal
ACON12356
AC ON12444
AC Max34
Rather than use SQL (I don't have data on SQL server) or re-organising data by order number, is there a fix using calc items, calc fields or grouping to show correct MAX figures? Why Excel doesn't simply evaluate visible cells seems crazy to me!
I've just started using Pivot Tables and have a problem right away. Couldn't find anything with the search function, but am not really sure, what to search for, either
Part of my data looks like this
NameLot #BinAmountABC123CB18C174.025 ABC123CB18C-24.975 ABC123CB18C-24.975 ABC123CB18C12.616 ABC123CB18C-12.616 ABC123CB18C-24.975 ABC123CB18C-24.100 ABC123CB18C-25.000 ABC123CB18C-25.000 ABC123CB18C-25.000
Now this is a material that is stored on a pallet with the code CB18C. The total sum of material that is added to/taken from this pallet adds up to Zero. My Pivot Table, however, believes the total is something like 2.84217E-14.
Sum of Amount NameLot #BinTotalABC123CB18C2.84217E-14 123 Total2.84217E-14ABC Total 2.84217E-14Grand Total 2.84217E-14
This problem occurs several times, the raw data adds up to Zero, but the Pivot Table calculates these tiny, tiny totals. Is this a known bug or can Pivots not handle 3 decimal data?
When I have two or more fields on the left of the pivot layout, sometimes I only want the line item data and the grand totals, but not the various subtotals.
Is there a way to suppress the various subtotals created by a pivot table?
Pivot tables. They have a habit of adding automatic subtotals to each field of data you drop into the pivot. Is there any way of setting the default to "none" rather than "automatic". It's driving me insane.
I am toying with the idea of writing some code to eradicate this issue, but if there's something native to Excel which will do the trick, that would be much more efficient.
attached Pivot Table. There is a section, which I've marked, that shows how I want my Pivot Table report to look. I've had Excel 2007 kick me out a number of times when I've tried to filter and shift rows and columns to accomplish this and areas are frequently grayed out. Can someone please assist?
I'm attaching a file rather than going into a lot of detail here because I think a visual is easier here. The raw data, my attempt at the Pivot Table and my desired outcome all show on the same tab.
Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.
View 2 Replies View RelatedI have an issue with a calculated item in my pivot table, because in the totals and subtotal it shows the sum of the column but I want to show the formula that I've specified to the calculated item.
For example, in the attached file, I have in rows the field "name" that has four values (A,B,C,D), in columns the field "Groups" that has "G1" and "G2". I add in the rows the calculated item "G1/G2" that has the formula G1/G2. The problem is that in the total the column "G1/G2" doesn't show the division of the total of "G1" and "G2".
I have a pivot table with 10 sub total rows, I would like to know if it is possibe to change the font into bold for rows who are subtotals.
View 6 Replies View RelatedI know there is some shortcut key that displays the + and - for expanding and contracting subtotals on the left side bar - so you don't have to use the new commands on the ribbon bar in 2007.
View 4 Replies View RelatedI 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.
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 RelatedUsing 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
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].....
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.
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.
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 RelatedI 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.
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 RelatedI 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?
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.
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.
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.