Pivot Table Assistance

[trevmac4]

Hi All,
I am trying to design a pivot table and subsequent "getpivotdata" within a workbook that I need assistance with.

First I have a table of customer data and in Cell $B$58 in sheet "Account Growth" I have a listbox with choices: "Existing", "Prospect" and "COI", I have a pivot table in $AL$1 of "Customer Information" sheet (which references my CustomerData in same sheet). I would like the pivot table to look at $B$58 as input ('Relationship')and then return all customer names that match $B$58 and put the names in the rows starting at $B$61. Below is snap of "Pivot table, with 'Row Label' as 'Customer Name' and input as 'Relationship'.

[TABLE="width: 755"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:.25in .25in .25in .25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-page-orientation:landscape;} td {padding:0px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl67 {text-align:left;} .xl68 {color:#0000D4; font-family:Calibri, sans-serif; mso-font-charset:0;} .xl69 {color:#0000D4; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:"\[<=9999999\]\#\#\#\\-\#\#\#\#\;\\\(\#\#\#\\\)\\ \#\#\#\\-\#\#\#\#"; text-align:left;} .xl70 {color:silver; font-family:Calibri, sans-serif; mso-font-charset:0; background:silver; mso-pattern:black none;} .xl71 {color:silver; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:"\[<=9999999\]\#\#\#\\-\#\#\#\#\;\\\(\#\#\#\\\)\\ \#\#\#\\-\#\#\#\#"; text-align:left; background:silver; mso-pattern:black none;} --> </style> [TABLE="width: 566"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:3413;width:80pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:4096;width:96pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:5632;width:132pt" width="132"> <col style="mso-width-source:userset;mso-width-alt:1877;width:44pt" width="44"> <col style="mso-width-source:userset;mso-width-alt:3370;width:79pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:5760;width:135pt" width="135"> </colgroup><tbody>[TR]
[TD="width: 80"]Relationship[/TD]
[TD="width: 96"](All)[/TD]
[TD="class: xl69, width: 132"][/TD]
[TD="class: xl69, width: 44"][/TD]
[TD="class: xl69, width: 79"][/TD]
[TD="class: xl68, width: 135"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67"]Hutterite Colony[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67"](blank)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67"]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Any help would be greatly appreciated.

Thanks,
Trevor
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Hi Trevor,

GetPivotData will only return numeric values from the data fields - it can't return text values from the rowfields which is what you are describing.

It will help if you could post a clearer example of your source pivot table that clarifies the relationship of the lookup and return fields, and the report layout type.

 

[trevmac4]

Hi JS411,
How can I attach my file for your reference?

Thanks,

 

[trevmac4]

JS411,
The columns I refer to are the ones listed as "Contact(s)", "Business Line", "Stage", "Opportunity", and "Notes" in the 'Account Growth' spreadsheet $B$61:$AR$61. For example I was thinking on using VLookup ("ContactName", CustomerInfo, etc) for each of the above since this information is in my 'Customer Information' table to populate these rows. The column "Expected" is a formula that multiple the( "Opportunity"*"Stage%") to give an expected opportunity dollars. This "Expected" is the only cell with a calculation that references a table of 'Opportunity Names' and subsequent %. I was looking to repeat the above for the table area under "Existing", "prospect" and "COI" in the Account Growth tab.

Does this make sense. My end goals of to be able to see a 'Sales Pipeline' in dollars based on stage of opportunity, classified by 'Existing' and 'Prospect'

 

[Jerry Sullivan]

Trevor, To answer your earlier question, this forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM). Generally for a question like this, a screen shot produced using one of the tools listed below in my signature is sufficient. In your OP image, it was just hard to for me to interpret which parts were your Pivot Table which were your desired result.

A few more clarifications will help
1. What version of Excel are you using? (this affects whether you can use xl2010's features of "Repeat item labels" and Slicers).

2. What report type do you have (Compact, Tabular or Outline)?

3. Does your PivotTable have report filters? If so, what are the names of those fields?

4. Is the layout of your PivotTable static, meaning the solution to your question may assume you aren't adding/removing fields, changing the order of fields. I understand the data in your PivotTable and the size of the PivotTable needs to be dynamic.

 

[trevmac4]

Hi Jerry,
1. I am using Excel 2003
2. Not sure of report type, can you explain this to me.
3. Filters: "Relationship", Row = "Customer Name"
4. My data table is dynamic, names will be added / deleted periodically.

Thank you,

 

[Jerry Sullivan]

Trevor,

Thanks for sending me your file, it helped me to understand what you were describing.

There's several approaches to do what you describe, and because you are already using macros in your workbook, that means VBA is an option as well.

Considering not only your current question, but also the direction it appears your project is going, I'd suggest this approach:

1. Use a VBA Worksheet_Change procedure to change the Report Filter whenever the Relationship type is changed in 'Account Growth'!$B$58.

2. Use a simple formula referencing the PivotTable to display the unique customers in the list starting at 'Account Growth'!$B$61.

For the macro, paste this code into the Sheet Code Module in Sheet "Account Growth"

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address <> "$B$58" Or _
        Target.Count > 1 Then Exit Sub

        
    On Error Resume Next
    With Sheets("Customer Information") _
        .PivotTables("PivotTable2").PivotFields("Relationship")
            .ClearAllFilters
            .CurrentPage = Target.Text
    End With
    On Error GoTo 0

End Sub

Enter this formula into 'Account Growth'!$B$61, then copy down.

='Customer Information'!AL5

You'll want to either remove the Grand Total from the PivotTable or modify the formula to ignore that cell.

=If('Customer Information'!AL5="Grand Total", "", 'Customer Information'!AL5)