Matching the min & max values for Primary and Secondary axes

Hi All,

I am constructing a tricky scrolling chart that makes use of dynamic named
ranges to allow me to chart a defined number of months of data (eg. 6 months
of sales figures) from a total of 30 available months.

This is all easy to understand and setup with offset formulae and custom
control scrollers etc.

What I would like to do is be able to plot two sets of data on the one chart
to compare them, for example sales $ and quantity for a single product.

Given the difference in type of data, these by necessity need to be plotted
on separate axes - no problem there.

If however I wish to plot the sales $ and profit $ for a single product, it
makes sense to 'synchronise' the min and max scale values for the two axes
so that the profit $ line displays as the appropriate % of the sales $
line...

How can I do this ??
I'd like to be able to switch between 'synchronised values' and non linked
values automatically, ie. if $ based information is present on both axes -
synchronise the scales otherwise leave them independent of each other.

I'd also like to be able to control the min and max values such that
regardless of which 6 month period I am scrolling through, the chart will
not 'jump' around to automatically optimise the axis scale.
I therefore would like to set the min chart value near to the lowest value
present in the 30 months of data and the max value to the highest value in
the 30 months of data.
How best can I do this ??

Now I am probably pushing my luck here, but is it possible to dynamically
vary the chart type depending on no. of months of data plotted ??
For example, if the user elects to plot less than 6 months of data, I'd like
it to display as a bar graph otherwise a line graph...


Thanks for any and all assistance.

Regards,

Neil

How good are your programming skills? ;-)

Since XL doesn't allow one to link chart scale parameters to cells (or named
formulas) you will have to resort to VBA code.

Once you decide to do that almost everything on your wish list becomes
feasible. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, [email protected] says...
> Hi All,
>
> I am constructing a tricky scrolling chart that makes use of dynamic named
> ranges to allow me to chart a defined number of months of data (eg. 6 months
> of sales figures) from a total of 30 available months.
>
> This is all easy to understand and setup with offset formulae and custom
> control scrollers etc.
>
> What I would like to do is be able to plot two sets of data on the one chart
> to compare them, for example sales $ and quantity for a single product.
>
> Given the difference in type of data, these by necessity need to be plotted
> on separate axes - no problem there.
>
> If however I wish to plot the sales $ and profit $ for a single product, it
> makes sense to 'synchronise' the min and max scale values for the two axes
> so that the profit $ line displays as the appropriate % of the sales $
> line...
>
> How can I do this ??
> I'd like to be able to switch between 'synchronised values' and non linked
> values automatically, ie. if $ based information is present on both axes -
> synchronise the scales otherwise leave them independent of each other.
>
> I'd also like to be able to control the min and max values such that
> regardless of which 6 month period I am scrolling through, the chart will
> not 'jump' around to automatically optimise the axis scale.
> I therefore would like to set the min chart value near to the lowest value
> present in the 30 months of data and the max value to the highest value in
> the 30 months of data.
> How best can I do this ??
>
> Now I am probably pushing my luck here, but is it possible to dynamically
> vary the chart type depending on no. of months of data plotted ??
> For example, if the user elects to plot less than 6 months of data, I'd like
> it to display as a bar graph otherwise a line graph...
>
>
> Thanks for any and all assistance.
>
> Regards,
>
> Neil
>
>
>

Tushar has a utility that scales a chart according to values you set in the
worksheet:

http://tushar-mehta.com/excel/softwa...art/index.html

On my site I have some VBA code that performs the same task, only you have
to set it up yourself, instead of letting Tushar's program do it for you:

http://peltiertech.com/Excel/Charts/...nkToSheet.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Tushar Mehta" <[email protected]> wrote in message
news:[email protected]...
> How good are your programming skills? ;-)
>
> Since XL doesn't allow one to link chart scale parameters to cells (or
> named
> formulas) you will have to resort to VBA code.
>
> Once you decide to do that almost everything on your wish list becomes
> feasible. {grin}
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>, [email protected] says...
>> Hi All,
>>
>> I am constructing a tricky scrolling chart that makes use of dynamic
>> named
>> ranges to allow me to chart a defined number of months of data (eg. 6
>> months
>> of sales figures) from a total of 30 available months.
>>
>> This is all easy to understand and setup with offset formulae and custom
>> control scrollers etc.
>>
>> What I would like to do is be able to plot two sets of data on the one
>> chart
>> to compare them, for example sales $ and quantity for a single product.
>>
>> Given the difference in type of data, these by necessity need to be
>> plotted
>> on separate axes - no problem there.
>>
>> If however I wish to plot the sales $ and profit $ for a single product,
>> it
>> makes sense to 'synchronise' the min and max scale values for the two
>> axes
>> so that the profit $ line displays as the appropriate % of the sales $
>> line...
>>
>> How can I do this ??
>> I'd like to be able to switch between 'synchronised values' and non
>> linked
>> values automatically, ie. if $ based information is present on both
>> axes -
>> synchronise the scales otherwise leave them independent of each other.
>>
>> I'd also like to be able to control the min and max values such that
>> regardless of which 6 month period I am scrolling through, the chart will
>> not 'jump' around to automatically optimise the axis scale.
>> I therefore would like to set the min chart value near to the lowest
>> value
>> present in the 30 months of data and the max value to the highest value
>> in
>> the 30 months of data.
>> How best can I do this ??
>>
>> Now I am probably pushing my luck here, but is it possible to dynamically
>> vary the chart type depending on no. of months of data plotted ??
>> For example, if the user elects to plot less than 6 months of data, I'd
>> like
>> it to display as a bar graph otherwise a line graph...
>>
>>
>> Thanks for any and all assistance.
>>
>> Regards,
>>
>> Neil
>>
>>
>>