Matching Secondary axis to Primary in dynamic chart

**rpena4** · 10-12-2016, 09:37 AM

Hello,
My challenge is that I have a stacked column chart, with an average trend line on the secondary axis. The chart is dynamic and controlled via slicers. Whenever a different slicer option is selected, the chart changes and updates with the corresponding data. This means that the scale for both axis changes independently based on the underlying data. However, I need the secondary axis to match the primary. For example, item #1 has a max value of 10 on the primary axis, and 5 on the secondary axis, while item #2 has a max value of 50 on the primary and 15 on the secondary. For item #1 I'd like both axis to show 10 and for item #2 for both to show 50. Simply changing the max value for the secondary axis does not work since when a different slicer selection is made, the axis won't change.

I'm at a loss here so any help would be greatly appreciated. Thanks in advance.

**MrShorty** · 10-12-2016, 10:24 AM

A couple of ideas:

idea 1: There is this tutorial about building a "proportional" secondary axis (http://peltiertech.com/secondary-axe...tional-scales/ ) that should work very well. The main complication I see is that is appears that this is in a pivot table, which has additional limitations. This essay (http://peltiertech.com/regular-chart...-pivot-tables/ ) discusses those limitations and how to work around them. Combining the principles on those two pages, one should be able to create a chart like you describe with good, proportional axis (where it sounds like you want the proportion to be 1).

idea 2: You could use VBA (probably in a change or calculate event) to adjust the secondary axis parameters as needed. This tutorial (http://peltiertech.com/link-excel-ch...lues-in-cells/ ) describes a procedure that reads axis parameters from the spreadsheet and writes them to the axis. The main adjustment in your case is reading the axis parameters from the primary axis and writing them to the secondary axis.

Not single click commands by any means, but, if you study these pages and follow Jon's examples carefully, you should be able to see how to develop one of those ideas into something you can use. Let us know where you get stuck and we'll help as best we can.