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

Matching the min & max values for Primary and Secondary axes
 

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 , 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

Matching the min & max values for Primary and Secondary axes
 

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" wrote in message
om...
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 , 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