• 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.

Pivot Chart, Trendlines, Slicers - can't get trendlines to 'stick'

staregirl

New Member
Hi all!PivotChartQuery1.PNG

I have a pivot chart that shows sales total by sales person/month, over three years.

It has slicers to select salespeople, years, quarters, types of sale.

I can add a trendline to each of my salespeople, but whenever I use any slicers/change the view of the data, my trendlines disappear:

PivotChartQuery2.PNG

I want to apply the trendline dynamically, so that regardless of the view my users select, the trend is still calculated and shown. The trendline acts more like formatting than a formula. I want to sort of 'marry' each trendline to each plotted line, if that makes sense?

Thanks in advance for any help!
 
Hi ,

Would it be possible for you to upload your workbook ?

I am not sure but this might be possible using VBA.

Narayan
 
It would be something like below in regular Module.
Code:
Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection

Set mySeriesCol = ActiveSheet.ChartObjects(1).Chart.SeriesCollection

For i = 1 To mySeriesCol.Count
    If mySeriesCol(i).Trendlines.Count > 0 Then
    Else
        mySeriesCol(i).Trendlines.Add
    End If
Next
End Sub

And then in Sheet2(Chart) module add
Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine
End Sub

This will add linear trend line to each series. Code also checks if there is trend line already present on the series. If it is, it will not add another.
 
Chihiro, thank you SO much, works like a charm and I am now feverishly brushing up my VBA skills so I can also be of help to someone soon!

Thanks again, really happy :)
 
Back
Top