Changing the Chart Title automatically when a value in Slicer is selected

**rv02** · 12-02-2014, 07:56 PM

Hi

I was wondering if anyone can help me. I have attached a very simple excel sample sheet with a pivot chart. I am trying to add a title to the chart which changes depending on what filter I use from my slicer. The tile should say "Sales per Department" and have the value of the slicer choice i.e. AAA or BBB or whatever is selected.

This can be either in the title or maybe at the bottom stating which departments have been chosen for the chart. My Main spreadsheet is very large but once I know how this done I can apply it to my main spreadsheet

I hope this makes sense.

Any help is appreciate it.

**shg** · 12-02-2014, 11:52 PM

Add a title to the chart, select it, and in the formula bar,

=Sheet1!$D$12

**rv02** · 12-03-2014, 03:00 AM

Thanks for this. However when no Slicer is selected he title only shows the first one i.e. AAA. Is there anyway to make it showing all the filters i.e. aaa,bbb,ccc and so on. Also can I add a text in front f the formula to make it a bit more meaningful something like "The sale for the period" and then show the filtes chosen.

Thanks n advance

**ELeGault** · 12-03-2014, 04:30 AM

You can - treat just like you do any other formula.

Either do it on the title when selecting OR make the referenced cell for the title build out exactly what you want to see -

="YOUR TEXT "&'Cell Reference'

**romperstomper** · 12-03-2014, 04:43 AM

You can use a UDF like this:

Please Login or Register  to view this content.

Which you call from a cell like this:
=GetSlicerVals(D11,"Dept")
(D11 should be the top left cell of your pivot table, and "Dept" is the slicer name)

Note: I had to change your pivot table to not retain old data to prevent the function from returning all the old items that used to be in the pivot table as well. I can't currently find a way to restrict the code to only what the slicer actually shows - even using the VisibleSlicerItems collection has no effect)

Your revised sample workbook is attached.