Auto format chart axis

[freud1]

Hi Guys,

I am making a chart to display a month's worth of data,

I have done this by creating 5 charts (one for each week)

Chart 1: 1st - 7th
Chart 2: 8th - 14th

and so on....

I am having some trouble with the formatting....

I want the charts to all be to the same axis, but if there are lower values between 8th - 14th then the axis will auto format to a different scale.

I cannot set it as manual upper limit because the data will not always be the same.

I need a way for excel to find the uppermost value in the entire series, then set the axis to accomodate this on all charts.

Thanks for any help..

[teylyn]

Hi,

find an empty cell in your sheet and calculate what the maximum axis value should be. Give this cell the range name Ymax
For good measure, you can also define cells for Ymin, Ymajor and Yminor. If you don't want these, just delete the respective lines in the code below.

Then add a button to the sheet and assign it this macro

Please Login or Register  to view this content.

You need to check the name of your chart and correct it in the macro, if required. To find the name of your chart, click outside the chart, then hold down the Ctrl key and click the chart. Now the chart name will be displayed in the name box above cell A1

hth

[freud1]

many thanks teylyn!

It works perfectly!

[jgray]

Hi -

I have a similar need, however, I need the macro to update all charts within a workbook based on those defined chart ranges. Any thoughts?

jacob