Is there a way to set the scale on a chart so that the primary maximum and minimum Y-values equal the same values on the secondary Y axis as well?
Basically I have a number of different stocks I'm looking at (historical log values), which each has its own line graph of the prices, and then I added a secondary series that shows 3 horizontal lines - the average price, and one standard deviation above and below the average so I can see the usual price range the stock fluctuates in (this series I have as a Scatter graph). My problem is that Excel seems to be using a different auto-scale for each Y axis (I've tried including the same minimum and maximum values in my secondary series, but the scale is still different sometimes).
Ideally, since I'm looking at quite a few different stocks, I'd like to automate this by adding a few columns where I could just set the maximum and minimum Y-values to equal the maximum or minimum price of each stock (for both the primary and secondary axes). I tried looking at the following site to see if I could create a macro (http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html) but my macro-writing skills are pretty deficient and haven't had much luck.
I realize this request might only be possible through VBA though, so basically long story short, would anyone know how to set up a macro that will enable my selected chart to autosize using the parameters I give it? (Say column A shows the dates, B shows the prices, and a section to the right (say cells D1:F4) would specify the Min and Max values of X and Y, and maybe the Tick size that the macro could read from? (I know how to create a blank macro, and then jump into the macro itself, but that's where I start getting a bit lost as to where to actually paste the code).