Display Filter selection in pivot chart title

I have Excel 2007 and want to display the items selected in the Pivot Chart filter on the chart title. So that if I select apples, it displays Apple Boxes Sold or if apples, oranges and peaches are selected then the chart title will display Apple Orange Peach Boxes Sold.
I know nothing about VBA but think it is the rout I will have to take to get it done.
Thanks,
Lee

Hi

I am looking for the same solution as Aggie81.

I have given my users a really cool dynamic pivot chart which they can mainpulate data using eight report filters, the only problem is there is no way of showing what you have filtered on, especially if it is multiple items, either in the title or even in a formula below the chart.

Does anyone have the knowledge to give a soultion to this really frustrating problem! Looks like VBA is the only answer.

Many Thanks

I'm interested in displaying the filter selection in the chart title as well.

Thanks in advance,
Mike

Hi,

I'm new here. But hope I can help you guys.

It's not quite complicated if you want to add a dynamic title on charts. Here is the macro you can modify and use in a Event of a Worksheet where a PT is residing.

Sub SetChartTitle()
For Each s In ActiveSheet.Shapes
If InStr(s.Name, "Chart") Then
s.Select
With ActiveChart.ChartTitle
.Characters.Text = "Market: " & Range("C4") & " State: " & Range("C3")
.Font.Size = 8
.Font.FontStyle = "Bold"
End With
End If
Next
End Sub

Hope this helps. I used it quite often and it worked for me.

John

Hi,

I tried to add a vertical line on a chart and make it dynamic, i.e., the line will move with the change in data. First of all, I have to add a line first.

I tried Jon Peltier's methodology but no luck.

My data like this, trying to add a V-line between -1 and 1, i.e., 0.

Thanks in advance.

John


Test_Cntl -6 -5 -4 -3 -2 -1 1 2 3
TEST 2.31545 2.28849 2.11074 2.36310 2.21455 2.29629 2.22503 2.18116 2.31045
CNTL 2.33816 2.28416 2.10368 2.34673 2.20748 2.26168 2.26475 2.15487 2.31083