Link to home
Create AccountLog in
Avatar of avoorheis
avoorheis

asked on

Using formulas in charts

Is it possible to use a formula in a chart, or, would I need to make another table for the chart data?
For example, I want to chart the max wait time by month (excel 2007):
Month  Rep   WaitTime
Nov     Jim     1:30
Nov     Joe     2:30
Nov     Jon     0:30
Oct     Jim     1:35
Oct     Joe     1:45
Oct     Jon     1:55
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

You will need to put the data in another table.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of avoorheis
avoorheis

ASKER

thanks guys, will look at the 2nd 2 suggestions soon.
alan
trying the pivot chart approach...is there a way to make the filter dynamic? like have it reference another cell.
as far as using the subtotal function; it's a good idea, but, since it requires inserting more cells, it won't work for me. Also, I'll need addtional filters, which need to be dynamic, so, for example, I would need to be able to find the max time between only Jim and Joe.
avoorheis,

>>for example, I would need to be able to find the max time between only Jim and Joe

Based on the original example, if the raw data are:

Month  Rep   WaitTime
Nov     Jim     1:30
Nov     Joe     2:30
Nov     Jon     0:30
Oct     Jim     1:35
Oct     Joe     1:45
Oct     Jon     1:55

would you expect the results to be:

Month  Rep   WaitTime
Nov     Jim     1:30
Nov     Joe     2:30
Oct     Jim     1:35
Oct     Joe     1:45

or:

Month     WaitTime
Nov          2:30
Oct          1:45

Patrick
is it possible to have the filter dynamic, say, based on another cell?
Say, I only needed one selection first off, so, I would either select one of the names in the drop down filter of the pivot, so, instead of selecting it manually, could it be based on another cell?
I really need you to answer my question from http:#a34473237 first
oops...the second one.
Month     WaitTime
Nov          2:30
Oct          1:45
seems like VBA would be the only solution to change the report filter automatically, I did find a couple of examples for that. Just seems like you'd be able, some how, to point the filter drop down to a cell (or maybe a range of cells if you want multiple selections).
I think this should work quite well.

Worksheet 'SourceData' contains two tables, 'Data' and 'Filter'.  Data contains the source data for the pivots, and Filter indicates whether or not to use a particular agent's data.

Sheet includes this event code to pick up addition of "new" reps:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cel As Range
    Dim tblData As ListObject
    Dim tblFilter As ListObject
    Dim RepName As String
    Dim UseRep As Boolean
    Dim NewRow As ListRow
    
    Set tblData = Me.ListObjects("Data")
    Set tblFilter = Me.ListObjects("Filter")
    
    If Not Intersect(Target, Me.[b:b], tblData.DataBodyRange) Is Nothing Then
        Application.EnableEvents = False
        For Each cel In Intersect(Target, Me.[b:b], tblData.DataBodyRange).Cells
            RepName = cel
            If Application.CountIf(tblFilter.ListColumns(1).Range, RepName) = 0 Then
                UseRep = (MsgBox("'" & RepName & "' appears to be a new Rep.  Do we want to add" & vbCrLf & _
                    "him/her to the filter?", vbQuestion + vbYesNo, "Unknown Rep") = vbYes)
                Set NewRow = tblFilter.ListRows.Add
                NewRow.Range.Cells(1) = RepName
                NewRow.Range.Cells(2) = UseRep
            End If
        Next
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window




Worksheet 'Pivots' contains a PivotTable and a PivotChart.


The workbook includes this event code to automatically update the pivots:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    ' By Patrick G. Matthews
    
    ' Feel free to use this code anywhere,  as long as you attribute authorship and the URL where you
    ' found it
    
    ' This event sub fires every time a sheet is selected.  The intent is to force a refresh of any
    ' PivotTables/PivotCharts on that sheet,  as it is possible that the source data may have changed.
    ' Thus this event attempts to ensure that the PT/PC is always in sync with its source data.
    
    ' Use care with this code if the data source is external; the frequent refreshes may not not be
    ' appropriate in that instance
    
    ' Code assumes that the source data and the PivotTable/PivotChart are never on the same worksheet!
    ' If the PT/PC are on the same worksheet as the source data, changes to the source data are NOT
    ' automatically reflected until the Workbook_SheetActivate event fires again
    
    Dim PT As PivotTable
    Dim ChtObj As ChartObject
    Dim Cht As Chart
    
    ' Based on the type of sheet activated (Worksheet, Chart, Excel4MacroSheet), run the appropriate
    ' code.  Please note that the Type property for a Chart will actually return the same value as
    ' for Excel4Macro sheets:
    ' http://excel.tips.net/Pages/T002538_Detecting_Types_of_Sheets_in_VBA.html
    
    Select Case Sh.Type
        Case xlWorksheet
            
            ' Loop through any PTs present on worksheet and refresh
            
            For Each PT In Sh.PivotTables
                PT.RefreshTable
            Next
            
            ' Loop through ChartObjects on worksheet.  If any are PivotCharts, refresh the underlying
            ' PT (which will also refresh the PC)
            
            For Each ChtObj In Sh.ChartObjects
                If Not ChtObj.Chart.PivotLayout Is Nothing Then
                    ChtObj.Chart.PivotLayout.PivotTable.RefreshTable
                End If
            Next
        
        Case Else
            
            ' As above, for Chart sheets, the Type property returns the same value as for Excel4Macro
            ' sheets.  Since this branch of the code gets both Chart and Excel4Macro sheets, we use
            ' the loop to avoid the error that would occur if we just tested Sh.PivotLayout directly.
            
            ' If the sheet really is a Chart, and if that Chart is a PC, then refresh the underlying
            ' PT (and thus the PC itself)
            
            For Each Cht In Me.Charts
                If Cht.Name = Sh.Name Then
                    If Not Sh.PivotLayout Is Nothing Then
                        Sh.PivotLayout.PivotTable.RefreshTable
                    End If
                    Exit For
                End If
            Next
    End Select
    
End Sub

Open in new window



That code is discussed in my article https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
Q-26711733.xlsm
avoorheis,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick