Creating Dynamic Charts

I'm reading up on what I can regarding dynamic dashboards and charts. And while i have got the basics of VBA down, I just can't seem to understand where to start in creating these dynamic charts.

I have attached some sample data that shows Revenues for 6 different departments for 49 different locations. This data is actually pulled from an online source where I need to set what I want to look at via a pivot table. The attached is a copy of that pivot data.

The idea here is to have a drop down or other selections where the user can view a clustered column chart. The user could either chose a specific location or region (which groups X number of locations) and then also be able to view the information either by Year, Quarter or Month/Period.

Note, I can't actually show which locations go to each region. The pivot data i receive doesn't do that way. so its either choose All properties, and then a location. Or chose a region and no actual location.

Ideally, I would love it if possible to show a comparative of this year,quarter, period vs last year, quarter, period. But not sure if the data is currently structured in a way to do that.

I've never been to strong with dynamic charts and learning this part as I go along.

If any has any suggestions, good tutorials, etc as well, I welcome them in trying to learn and create this.

Thanks
PT

I am not exactly sure what you want, but here is a thought to get started.

I converted the data into an Excel Table (because I intend to use slicers) and I removed the total column and the total row. I highlighted the whole thing and made a totally incomprehensible chart ... until you you start filtering things out which I do with slicers. Do a web search on slicers, there are dozens of tutorials out there, but they are intuitive and very powerful. Selecting one slicer limits what you can select on other slicers. In the example I am looking at the first half of 2015.

You can cut / paste the chart and the slicers onto their own sheet so you can expand the chart.