• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Update Pivot Chart target vs actual via dropdown

Justin L

New Member
In the attached file I made a pivot table to chart the progress of multiple tasks. I want to be able to select the task from the dropdown and have the pivot chart automatically update with the running totals for that task. I tried many variations of the code I found here (similar here) but can't figure out how to connect the task to the target & actual for that task. I'm six hours into it with nothing to show. The code doesn't seem to do anything at all. Your help is greatly appreciated.
(note: I am using Excel 2007)
 

Attachments

  • Substitution Level 2.xlsm
    52.9 KB · Views: 4
Hi:

I do not think this is possible since your tasks are not linked to the pivot on what basis you want to filter the pivot.
Thanks
 
Replace the code in the Dashboard sheet with this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'Set the Variables to be used
    Dim pt                    As PivotTable
    Dim Field                 As PivotField
    Dim NewCat                As String
    Dim vMatch
    Dim sSuffix               As String

    'This line stops the worksheet updating on every change, it only updates when cell
    'H6 or H7 is touched
    If Intersect(Target, Range("e13:k14")) Is Nothing Then Exit Sub
    If LenB(Range("e13").Value2) = 0 Then Exit Sub

    On Error GoTo reset_all

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    vMatch = Application.Match(Range("e13").Value, Range("B1:Q1"), 0)

    If Not IsError(vMatch) Then
        'Here you amend to suit your data
        Set pt = Me.PivotTables(1)
        With pt
            .ManualUpdate = True
            For Each Field In .DataFields
                Field.Orientation = xlHidden
            Next Field
            If vMatch = 1 Then
                sSuffix = vbNullString
            Else
                sSuffix = Int((vMatch + 1) / 2)
            End If
            .AddDataField Field:=.PivotFields("Target" & sSuffix), Caption:="Target ", Function:=xlSum
            .PivotFields("Target ").Calculation = xlRunningTotal
            .AddDataField Field:=.PivotFields("Actual" & sSuffix), Caption:="Actual ", Function:=xlSum
            .PivotFields("Actual ").Calculation = xlRunningTotal
            .ManualUpdate = False
        End With
    End If

reset_all:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub
 
Hi:

You do not need a macro for this. Just rearrange your data you will get the desired result.

Thanks
 

Attachments

  • Substitution Level 2.xlsm
    66.1 KB · Views: 5
Back
Top