Show AutoFilter selection in Graph Label

[JPM]

I have a large table with sales data from different States in the U.S. In order to make the data meaningful to me I use AutoFilter to look at one State at a time. I also have a graph that is drawing its data from this table.

Is there a way to show in the Graph which state I have selected in AutoFilter?

Thanks in advance.
JPM

 

[Oaktree]

Have you considered creating a pivot chart?

 

[JPM]

I tried that but that's not really working because I don't want to sum or count etc. the data for a state, all I want is to show me the data for the selected state --filter it. I guess I should have mentioned that I am using a Scatter chart, which does not work with pivot data.

Thanks though for the suggestion.
JPM

 

[Jon Peltier]

You can make a regular chart from pivot data, enabling your XY chart. Select a blank cell located away from the pivot table, and start the chart wizard. In step 1, select the XY chart type you want. In step 2, click on the Series tab, and enter each series and its parameters here. When you change the pivot table, the chart keeps looking at the same cells, so it updates just fine.

Here's a hint: put the state field into the page area, and use the cell with the state's name as the name of the series.