Free Microsoft Excel 2013 Quick Reference

Show notes in pivot table Results

Hi, I am using Excel 2007.
I was wondering if anyone knew of a way to show NOTES into a Pivot Table Column?

The cut-down version of the scenario is - I have 5 columns in an excel table - Category, Item, Units, Rate, Total & Notes.

1) A CATEGORY can have multiple ITEMS, however, there is only ONE entry of an ITEM inside a CATEGORY - i.e. item is unique inside the bounds of a category.
2) Every ITEM has # of units in UNITS column
3) RATE column has price per unit
4) TOTAL is RATE x UNITS and
5) The NOTES column is to write anything that may be specific to the ITEM.

Eg.
CATEGORY: Room; ITEM: Chairs; UNITS: 5; RATE: $100; NOTES: Black Leatherite chair.

Now, I am using a Pivot Table to send a QUOTE to the customer, like -
REPORT Filter - NONE
Column Labels - Sum of Values
Row Labels - Category & Item
Values - Total

I want to be able to show the NOTES against each item, however, if I add it to the 'Values', it only gives me the options to show Count, Sum, Deviation etc etc and there isn't a way to show the ACTUAL content.

Does anyone know of a way to show NOTES in this Pivot table? I need these NOTES as part of the quote, which may be used to highlight a feature of the item.

My reason(s) for using Pivot Table is that the 'Calculation' spreadsheet has innumerable columns (what I explained above is about 1/10th of it) and Pivots generally consolidate information well and show them nicely in categories, using indentation etc.

Any help would be much appreciated.

Thanks in advance.

----
Regards,
Nitin Malhotra

Hi,

I am working in a Pivot table. I have data analysed by each month of years
2000-2005. I hide monthes 1-12 for years 2000 - 2003 so that only the total
of the year shows. But in year 2004 I only want to hide monthes 1-10, that is
i want months 11 and 12 to show. If I hilight a month and click on "hide
detail" it hides all 12 months (and for all years). why is this happenng.

I know i could just select the hole columns (outside the pivot table) and
hide them but isn't there a more elegant way?

Please note that whicherer the display options i do want all data to be
calculated in the final outcome.

Thank you.

Using Excel 2010 I have created a pivot table and I am matching the items to be displayed with code that refers to .PivotItems(x). I have a list of raw data that has 13 entries, and these show up in the pivot catagory filter. However, with the VBA code I can see that the count of pivot catagory items is 14. When I output the full list I noticed that there are 2 enties for the same line of data in the format:

Region X & Y (item 13)
Region X & Y (item 14)

Now the selected item is item 14, but because item 13 does not exist as a selection(I have checked and recoded that raw data to remove the ampersans), the value of x never iterated to 14 which is the item I want to show/hide.

Cliff notes:

In summary using code in the format:

thePT.PivotFields(PivotCatFiler).PivotItems.Count

Resolves to 14 when there are only 13 possible selection in the raw data and the pivot table catagory filter.

Has anyone ever seen this?

I would like to adapt Andy Pope's code to display the last X days of call data in a pivot table - starting from the most recent date - instead of just today's data which this code displays. Ideally, I would like the user to be able to enter the number of days of data they wish to see by entering a number in a cell and have the code pick it up from there. Ex. enter 14 and have the pivot table display the last 14 day's worth of calls. If the code could also skip calls made on Sat or Sun it would be even better


	VB:
	
 Macro1() 
     '
    Dim pvtItem As PivotItem 
    Dim strDate As String 
     
     ' check  date  format - especially single digit days 01 or 1
     
    strDate = Format(Now(), "mm/dd/yyyy") 
    With ActiveSheet. PivotTables("PivotTable1").PivotFields("Date") 
        .ShowAllItems = True '
        For Each pvtItem In .PivotItems 
            pvtItem.Visible = (pvtItem. Name = strDate) 
        Next 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code is from http://www.ozgrid.com/forum/showthread.php?t=59793

I have seen many posts on displaying pivot items as well as read all of;
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
but I cannot get them to work the way I need them to.
Your help, as always, is greatly appreciated.

Please note there are usually about 50,000 rows of call data in the DATA sheet the pivot table reads from at roughly 1,000 calls per day so when a user selects the most recent 7 days to display, the pt is selecting some 7000 records to work with. The call dates are stored in a column called DATE on the DATA sheet. The pivot table is called ptAGENT on a sheet named AgentMaster

Hi All,

I need to add checkboxes to the items in the page field in a pivot table in excel 2003 in order to allow multiple selections. Apparently this option is available in 2007.

I have scoured the web for a solution but all have involved hiding or showing items by moving the page field to the row field and then back again. This will not work in the present context as I need to group items in the row field and then lock that group so that it cannot be altered, and then move the options in that group to the page field where users (with very basic computer skills) can make multiple selections rather than just all or one, as the default set up allows.

If anyone knows where I can find some VBA code to do this then I would be very thankful. I am surprised that I have found it so difficult to come across. Note that the code at http://www.contextures.com/xlPivot03.html does not do the job.

Many thanks,
Carl.

PS. I have the code to lock the Row fields thanks to contextures.

That the link for the excel sheet which shows the figers and idea abt the problem

http://www.freewebs.com/imagea/file.jpg

Hi
here is the data . these all data is in pivot table and as you can see i can not get the turnover from different months.but he is showing the turn over form same months.
All i want is that this pivot table should enable to compare to different months and show me the turn over as his result.
note (the turn over will be the sum of the prices of both year )

Can You help me how to do it

I have some text in a cell that is up to 1000 characters.
I then make a pivot table out of it with the code below but it cuts it off a 256.
Any ideas?
The column is called Note

Sub MakePivotTable()

' PivotTable

    Dim ws As Worksheet
    Dim iCount As Integer
    Dim varController As String
    
    Application.DisplayAlerts = False 'turn off the delete prompt for worksheets
    Application.ScreenUpdating = False 'True for debugging
    Application.Cursor = xlWait
           
    Sheets("Sheet1").Select
    varController = frmSelectController.cmbCreditControllers.Value
    
    If varController = "" Then
        frmSelectController.Hide
        frmControllers.Show
        Exit Sub
    End If
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=xx.x.x.xx;APP=Microsoft Office 2003;DATABASE=CCApp;Trusted_Connection=Yes"
_
        , Destination:=Range("A1"))
        .CommandText = Array("exec ccapp_ledgerreport '" & varController & "'")
        .Name = "Query from 10.4"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
    '---------------------------------------------------------
    'find out number of rows
    '---------------------------------------------------------
    'Get the total number of rows in the spreadsheet so we know where to copy the formula to.
    Dim intLastRow As Integer
    intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    'MsgBox "Last row is " & intLastRow
    
    '---------------------------------------------------------
    ' macro
    '---------------------------------------------------------
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Outstanding Amount"
    With ActiveCell.Characters(Start:=1, Length:=18).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-13]=R[1]C[-13],"""",RC[-1])"
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2", "P" & intLastRow)
    Range("P2", "P" & intLastRow).Select
    Range("L12").Select
    Application.CommandBars("PivotTable").Visible = True
    
    'ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    '    "Sheet1!R1C1:R518C16").CreatePivotTable TableDestination:="", TableName:= _
    '    "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R[" & intLastRow & "]C16").CreatePivotTable TableDestination:="",
TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "accountcode", "cust_name", "invoice_no", "inv_date", "Status",
"project_no", _
        "projdesc", "projman", "notedate", "Note"), PageFields:="costcent"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Outstanding Amount")
        .Orientation = xlDataField
        .Caption = "Sum of Outstanding Amount"
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("cust_name").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("C4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("invoice_no").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("D4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("inv_date").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("E4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Status").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    Range("F4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("project_no").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("G4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("projdesc").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("H4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("projman").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    Range("I4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("notedate").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("B8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable1").Format xlTable7
    ActiveWindow.SmallScroll ToRight:=9
    Range("J3").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("accountcode")
        .Orientation = xlRowField
        .Position = 1
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("B:B").Select
    Columns("K:K").Select
    Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    Range("K8").Select
    ActiveWindow.DisplayZeros = False
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Data"
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "Whole Ledger"
    Range("C22").Select
    '-----------------------------------------------------------------------------------------------------
    'v1.0.3 addition
   
ActiveSheet.PivotTables("PivotTable1").PivotFields("costcent").PivotItems("(blank)").Visible =
False
    '-----------------------------------------------------------------------------------------------------
    ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="costcent"
    Sheets("Whole Ledger").Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "accountcode", "cust_name", "costcent", "invoice_no", "inv_date",
"Status", _
        "project_no", "projdesc", "projman", "notedate", "Note")
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("C3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("costcent").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    
    For Each ws In ActiveWorkbook.Sheets
        'MsgBox (ws.Name)
        If ws.Name = "(blank)" Then
            ws.Delete
        End If
    Next ws
    
        For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "Whole Ledger" Then
            ws.Cells.EntireColumn.AutoFit
            ws.Columns("I:I").ColumnWidth = 20
            ws.Columns("J:J").ColumnWidth = 50
            ws.Columns("J:J").WrapText = True
            ws.Columns("K:K").ColumnWidth = 20
        Else 'it's the whole ledger worksheet
            ws.Cells.EntireColumn.AutoFit
            ws.Columns("J:J").ColumnWidth = 20
            ws.Columns("K:K").ColumnWidth = 50
            ws.Columns("K:K").WrapText = True
            ws.Columns("L:L").ColumnWidth = 20
        End If
        
        'page setup to landscape
        With ws.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0.511811023622047)
        .FooterMargin = Application.InchesToPoints(0.511811023622047)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 100
        .PrintErrors = xlPrintErrorsDisplayed
        End With
    Next ws
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Range("A1").Value = "Report run on: " & Now()
    Range("A1").Select
    
    Application.Cursor = xlDefault
    
    Call SaveWithoutMacros
    
    'now close the workbook without saving
    'no need to delete data
    ThisWorkbook.Close savechanges:=False
    
End Sub


Hello,

I have a pivot table that essentially shows the number of hours a resource worked for every week of 2011. I am trying to come up with a formula that will look through all the weekly hours and note if a resource has worked over 40 hours. I am having an issue with this, can anyway provide some guidance on if this can be done?

Formula Name Week 1 Week 2 Week 3 week 4 John 30 45 39 40 Jane 40 46 44 32
What would the formula be in the above example? The result could state "Over 40".

Is there another formula that could give me a result that reflects which weeks the resource was over 40 and maybe another one to tell my what the hours were for those weeks?

Thank you in advance,
Kim

Hi,

I am working in a Pivot table. I have data analysed by each month of years
2000-2005. I hide monthes 1-12 for years 2000 - 2003 so that only the total
of the year shows. But in year 2004 I only want to hide monthes 1-10, that is
i want months 11 and 12 to show. If I hilight a month and click on "hide
detail" it hides all 12 months (and for all years). why is this happenng.

I know i could just select the hole columns (outside the pivot table) and
hide them but isn't there a more elegant way?

Please note that whicherer the display options i do want all data to be
calculated in the final outcome.

Thank you.

Hi,

I have a workbook, with 2 worksheets. MS Excel 2003

One holds data, the other is a pivot table for that data.

I have a macro setup to refresh the pivot table and do some other things to it.

My issue is two fold. First, while creating the pivot table, I used some test data. With this data now gone, my pivot table row is still holding on to items that were in the test data.

For example, the test data pivot row had list selections:

A
B
C
D

My new data only has:

A
B

But the drop down list still shows the A-B-C-D in the list, annoying.

I found the fix for that to be remove the ROW item from the pivot table, refresh the table, and add it back. That does fix the list problem.

However my problem is that this original pivot table has some kind of conditional formatting with it that I want to keep, and the above trick removes this formatting as well.

This desirable formatting turns certain values RED if they exist.

For example if "AB" "AC" "AD" etc exist in column c of the pivot table, it turns them all RED in the pivot table, even when new data is added.

With this original desired effect that I lose when I remove the undesired effect, if say I set the value "E" to blue in the pivot table, add a bunch of data and refresh, it will set all "E's" to blue all by itself!

I can't for the life of me figure out how it is doing this as I can see nowhere that conditional formating is set! Its not set in the pivot tabel cells, not in VBA, not in the data sheet, not in my macros!

How is this magic formatting happening?

Note: upon saving the original workbook, I do get an "update external data" message, not sure if this could be a link somehow or not.

Thanks much,

Mark

Hi,

I am trying to use the drop down list to select the priority and then my graphs should show the data based on the priority selected.

I have 3 sheets in my workbook. Sheet1 is for all the data, sheet2 is for the summary, and sheet3 is for graphs. In this sheet3, i have the drop down list for me to select the priority.

Look likes it is working when i select P1, P2, and P3. However, i want my graph to show all data from P1, P2, and P3 whenever i selected ALL from the drop down list.

Unformately, i have no idear of how to get this done. I have attached a workbook along.

Note: this post was create in other forum and the solution was to go with Pivot Table and Chart. However, i am pretty new to Pivot Table & Chart and i would like to go formula base. The link can be found here http://www.excelforum.com/excel-gene...-new-post.html

Thanks in advance and sorry for inconvenience.

Regards,
sanlen

I want to create a dynamic Pivot Table based on a search criteria from a Userform. The criteria will be a date search (from and to dates Ė 2 separate text boxes). When submitting the Userform, all records in the user-selected range will then be displayed in the Pivot Table, for example show all records between dates 1/3/11 and 31/3/11.

All the source will be coming from the same Excel Worksheet (no external data).

Will this require mounds and mounds of VBA coding, or is there a simpler solution.

Note: Iím at the Novice stage of VBA/SQL?

(In anticipation) Many thanks

I have a sheet that comes from a pivot table that I have to format each month. It's a pain I was hoping a macro might be able to do. I was told this is the place to come for this. I have attached the the sheet unformatted and formatted. Note, there are 2 more columns on the finished sheet I add in to show totals. Also note col A has to be re-ordered each time (Safety, Environmental, Security, Vehicles, Operations).

Not sure about the complexity of this. Let me know. And let me know if I need to supply more info.

Dynamically Sourced Range Pivot Tables with Excel VBA.
A common problem with automating pivot tables is how to make the VBA generic so that when the number of rows or columns changes that the code sitll works. In this example it shows how to make a pivot table no matter how many rows or colums there are and also how to place this pivot table in a specific destination. Enjoy.

download sample book
http://programminglibrary.com/Progra...ta sources.xls


	VB:
	
 CreatPivot() 
    Call AutoPivot("Data", "Name", "Animal Type") 
End Sub 
Sub AutoPivot(strPivsheet As String, strPivCol1 As String, strPivCol2 As String) 
     'USE-EXCEL VBA CREATE SIMPLE PIVOT TABLE FROM COLUMNIZED DATA REGARDLESS OF NUMBER OF ROWS OR COLUMNS USING DYNAMIC RC
NOTATION
     'CREATED BY MARK SLOBODA
     'DOWNLOAD COOL EXCEL STUFF :http://programminglibrary.com/Programming%20Library/DOWNLOAD/downloads.aspx
     
     '************NOTES*****************************
     '1)  ASSUMES DATA STARTS IN 1,1
     '2)  IN THIS EXAMPLE YOU ARE USING VARIABLES TO MAKE DATA SOURCE SIZE OF NUMBER OF ROWS AND COLUMNS AND SHEET REGARDLESS
OF SIZE
     '3)  YOU COULD PASS AN ARRAY OF PIVOT COLUMN FIELD NAMES OR SIMPLY ADD OTHERS
     
     '**************** VARS PASSED *****************
     'strPivsheet - SHEET WHICH PIVOT TABLE WILL BE CREATED FROM
     'strPivCol1 - PivotColumn1 (Used for Row and Data Fields)
     'strPivCol2 - PivotColumn2 (Used for Column Field)
     
     'DEC VARS
    Dim lngR As Long '#ROWS-ALWAYS USE LONG TO AVOID MAX INTEGER ERROR
    Dim intC As Integer '#COLUMNS
     
     'SET VALS
    Sheets(strPivsheet).Select 'SELECT SHEET TO CREATE PIVOT TABLE ON
    Range("A1").Select 'SELECT A CELL IN ACTIVE DATA RANGE
    lngR = Range("A1").CurrentRegion.Rows.Count 'GET ROW COUNT ON ACTIVE SHEET
    intC = Range("A1").CurrentRegion.Columns.Count 'GET COLUMN COUNT ON ACTIVE SHEET
     
     'BEGIN CODE
     'CREATE THE PIVOT 2 VERSIONS
     
     '******************* VERSION 1 NO SPECIFIC DESTINATION SHEET ********************************
     '********************************************************************************************
     
     'CREATE PIVOT
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ 
    "'" & ActiveSheet.Name & "'!R1C1:R" & lngR & "C" & intC, TableDestination:="", TableName:="PivotTable1" 
     
     'ADD CENTER DATA FIELD!!! IMPORTANT - DO THIS FIRST FOR PIVOT ROW AND COLUMN FIELDS TO WORK CORRECTLY - PASSED VARIABLE
USED FOR PIVOT COLUMN
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields(strPivCol1), "Count of" & strPivCol1, xlCount 
     
     'ADD ROW FIELD (LEFT HAND VERTICAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT ROW FIELD
    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol1) 
        .Orientation = xlRowField 
        .Position = 1 'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
    End With 
     
     'ADD COLUMN FIELD (TOP HORIZONTAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT COLUMN FIELD
    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol2) 
        .Orientation = xlColumnField 
        .Position = 1 'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
    End With 
     
     
     '******************* VERSION 2 SPECIFIC DESTINATION SHEET ********************************
     '*****************************************************************************************
     
     '    Sheets.Add    'CREATE A NEW SHEET OR ASSIGN EXISTING ONE TO VARIABLE
     '    strDynamicSheet = ActiveSheet.Name    'ASSIGN DYNAMIC SHEET
     '    Sheets(strPivsheet).Select    'GO BACK TO PIVOT DATA SHEET
     '
     '    'CREAT PIVOT
     '    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
     '                                 "'" & ActiveSheet.Name & "'!R1C1:R" & lngR & "C" & intC, TableDestination:="'" &
strDynamicSheet & "'!R1C1", TableName:="PivotTable1"
     '
     '    'ADD CENTER DATA FIELD!!! IMPORTANT - DO THIS FIRST FOR PIVOT ROW AND COLUMN FIELDS TO WORK CORRECTLY - PASSED
VARIABLE USED FOR PIVOT COLUMN
     '    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
     '                                                        "PivotTable1").PivotFields(strPivCol1), "Count of" &
strPivCol1, xlCount
     '
     '    'ADD ROW FIELD (LEFT HAND VERTICAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT ROW FIELD
     '    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol1)
     '        .Orientation = xlRowField
     '        .Position = 1    'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
     '    End With
     '
     '    'ADD COLUMN FIELD (TOP HORIZONTAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT COLUMN FIELD
     '    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol2)
     '        .Orientation = xlColumnField
     '        .Position = 1    'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
     '    End With
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need some VB code for a marco I run with Pivot Tables.

I would like it to:
Drag to hide - all data fields (only)
Drag to data - 1st 23 field buttons in position order they appear in the wizard.
Auto change all 23 field buttons from count to sum.

My pivot table consists of the following:

Column Fields - Data
Row Fields - Item #, Description
Data Fields - Past Due + 22 Dates

When recording this macro I found something that boggles me. The macro did not follow my commands which I do not understand why. In the code below - I think the macro made assumption.

The steps I took when creating the macro are as follows -
1 Opened pivot table wizard.
2. Went into layout
3. Dragged Item # from Row to pivot field.
4. Dragged Description from Row to pivot field.
5. Dragged Past Due and 21 dates from Data to pivot fields.

Note - I dragged Past Due to pivot field first - then 3/4, which is reverse to what the macro shows.
At this point the layout is completely empty.

6. Dragged Item # from pivot field to Row
7. Dragged Description from pivot field to Row
8. Dragged Past Due and 22 dates seperately from pivot field to Data changing each from Count of to Sum of.
9. Selected finish.

I have found several issues with this macro -
A. It is not following the steps I requested it to do.
B. It apprears that it breaks up the steps rather than completing the task. No where in the macro does it show me pulling Item # & Description off of the layout nor putting them back.
C. The macro orientates, changes caption, & funtion prior to showing position for all pivots which is backward to what the macro should have shown. I moved it before I changed it.

	VB:
	
 ActiveSheet.PivotTables("PivotTable1").PivotFields("6/14/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 6/14/2004" 
    .Function = xlSum 
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
    "Sum of 6/14/2004").Position = 23 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I know this is long and drawn out - Any idea's would be useful at this point.

Original Recorded Macro


	VB:
	
 Macro120() 
     '
     ' Macro120 Macro
     '
     '
     
     '
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Item #", _ 
    "Description"), ColumnFields:="Data" 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 6/7/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/31/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/24/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/17/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/10/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/3/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/26/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/19/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/12/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/5/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/29/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/22/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/15/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/13/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/12/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/11/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/10/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/9/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/8/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/5/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/4/2004"). _ 
    Orientation = xlHidden 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Past Due"). _ 
    Orientation = xlHidden With ActiveSheet.PivotTables("PivotTable1").PivotFields("Past Due") 
    .Orientation = xlDataField 
    .Caption = "Sum of Past Due" 
    .Position = 1 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/4/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/4/2004" 
    .Position = 2 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/5/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/5/2004" 
    .Position = 3 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/8/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/8/2004" 
    .Position = 4 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/9/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/9/2004" 
    .Position = 5 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/10/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/10/2004" 
    .Position = 6 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/11/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/11/2004" 
    .Position = 7 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/12/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/12/2004" 
    .Position = 8 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/13/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/13/2004" 
    .Position = 9 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/15/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/15/2004" 
    .Position = 10 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/22/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/22/2004" 
    .Position = 11 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/29/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 3/29/2004" 
    .Position = 12 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/5/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 4/5/2004" 
    .Position = 13 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/12/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 4/12/2004" 
    .Position = 14 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/19/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 4/19/2004" 
    .Position = 15 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/26/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 4/26/2004" 
    .Position = 16 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/3/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 5/3/2004" 
    .Position = 17 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/10/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 5/10/2004" 
    .Position = 18 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/17/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 5/17/2004" 
    .Position = 19 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/24/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 5/24/2004" 
    .Position = 20 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/31/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 5/31/2004" 
    .Position = 21 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("6/7/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 6/7/2004" 
    .Position = 22 
    .Function = xlSum 
End With 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("6/14/2004") 
    .Orientation = xlDataField 
    .Caption = "Sum of 6/14/2004" 
    .Function = xlSum 
End With 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of Past Due").Position = 1 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/4/2004").Position = 2 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/5/2004").Position = 3 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/8/2004").Position = 4 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/9/2004").Position = 5 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/10/2004").Position = 6 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/11/2004").Position = 7 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/12/2004").Position = 8 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/13/2004").Position = 9 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/15/2004").Position = 10 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/22/2004").Position = 11 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 3/29/2004").Position = 12 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 4/5/2004").Position = 13 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 4/12/2004").Position = 14 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 4/19/2004").Position = 15 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 4/26/2004").Position = 16 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 5/3/2004").Position = 17 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 5/10/2004").Position = 18 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 5/17/2004").Position = 19 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 5/24/2004").Position = 20 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 5/31/2004").Position = 21 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 6/7/2004").Position = 22 
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _ 
"Sum of 6/14/2004").Position = 23 
ActiveWorkbook.ShowPivotTableFieldList = True 
ActiveWorkbook.ShowPivotTableFieldList = False 
ActiveWindow.SmallScroll Down:=0 
Range("E20").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

Stapuff

Attached Pic show a Pivot table with row labels of Ln, Pos, Type.

Note that in Pos 4 of Ln A there was data. There were 2 CDs and 3 HIs. The pivot table correctly displays.

There were no TYPES of any kind in POS 0-3 of Ln A. But the "EMPTY BINS" are shown. I have it set so that if I were to manually add an AB, or CD, or EF, or GH, etc... it would display as 1 and the other 5 would go away. The way Pos 4 has.

but the data source is a live data field... How can I tell the pivot table that if there is NO data point that meets the Type or POS criteria shown, then don't show those Bins?? I'd be perfectly happy if it would just collapse the types and show the Pos as either 0 or blank.

Any thoughts??, do you know what I'm asking?

Thank you all,

Pivot Table Groupings.JPG

Thomas

I am using the external data sources link to link data into Excel 2007 to then use Pivot tables to analyse the data - all is working well.

I have all my data showing in the "values" section as you would expect but want to use filters if possible to limit what shows in the table.

For example

I have a list of customers who have invoices and credit notes.
Not all customers have a credit note showing yet in the data.
I would like the pivot table to show only those customers who have a credit note showing on their account and total this up accordingly.

I tried using a std filter in excel (not in the pivot table) but this didn't work as it messed up headings etc in the pivot table - e.g if first customer had no credit note you lose the first "header" information.

Help

Ed

I just joined and I already have another question.

I am making a huge contact list which I wish to be able to search through using a pivot table as that is the best way I can think of.

So at the top of the pivot talbe in the page field column I want to have

Country
State
City

and so forth.

When someone picks the drop down menu for the USA, I want the state drop down menu to only show the States that are in the USA. When someone picks a State in USA I only want the city drop down list to list cities in the state chosen before.

I attempted to make a macro that would just filter the source date when you changed each field but it did not work.

Any thoughts?

Thanks!

(On a side note, this option should really be included in excel in the first place as I imagine I am not the first to ask (although I have searched the forum and internet for solutions, non seem to work))

I'm trying to show % change from year-to-year, with years in columns in a pivot table. The source is a .cub file. To show % change, I'd need a calculated item or row, but you can't create that from a pivot table based on an OLAP database. Since my first approach failed, instead I created a pivot table of the same data from SQL server. With this approach, I was able to create calculated items that show the percent change from column to column. However, to also show the total in a row, I have to enable "grand total" -- which generates a very inaccurate % change in that grand total row...and now I'm struck writing a note on the report to "disregard the growth rates in the grand total row"--not a great solution. Also, I notice the pivot table generated from SQL server has lost all knowledge of how dimensions relate to each other (for example, that certain countries belong in a particular region)--and if I could figure out how to make this pivot table not display zero rows, that intelligence would return, as there is not data in meaningless combinations--but I can't figure out how to eliminate rows with no data, and that feature seems to be grayed out in the place I can get it to show up.
In short, I can't produce a decent presentation of data using either a pivot table connected to an OLAP cube, or one with the data fully included in the Excel file. And I don't know exactly which gremlins I'm fighting here...thanks for any help.

Hi All,

I hv manage to generate sales change using pivot table , but i fail to generate profit change using pivot table. not sure why ?

Sheet4

 ABCDE3Sum of profit in %Year_key   4group_key20062007changeGrand Total5G0#DIV/0!0.7364516130.7364516130.7364516136G10.3719761140.3380294420.2670408760.3380294427G20.3524988320.3758987640.3114614720.3758987648G30.4142175410.4003508880.3976786590.4003508889Other0.2950941730.2443021830.2060276140.24430218310Grand Total0.3602009030.3597767660.3582767440.35977676611     12 0.338029442   13less0.371976114   14should be -0.03394667   15     16But it show0.267040876   17     18my change formula = "2007" - "2006"    19     20where go wrong ?    21     
Spreadsheet FormulasCellFormulaB12=+GETPIVOTDATA("profit in %",$A$3,"Year_key",2007,"group_key","G1")B13=+GETPIVOTDATA("profit in %",$A$3,"Year_key",2006,"group_key","G1")B14=B12-B13B16=+GETPIVOTDATA("profit in %",$A$3,"Year_key","change","group_key","G1")

Excel tables to the web >> Excel Jeanie HTML 4

the formual i use was :-

Sheet5

 ABCDEFGH1Calculated Field       2Solve OrderFieldFormula     31profit in %=profit_amt /sls_amt     4        5Calculated Item       6Solve OrderItemFormula     71change='2007' -'2006'     8        9        10Note:When a cell is updated by more than one formula,      11 the value is set by the formula with the last solve order.      12        13 To change the solve order for multiple calculated items or fields,      14 on the Options tab, in the Tools group, click Formulas, and then click Solve Order.      

Excel tables to the web >> Excel Jeanie HTML 4

regards

Paul Yeo