Dynamic chart axis limits from cell values?

[N1GHTEYES]

In Excel, does anyone know if there any way to assign the chart axis min and max values to cell values?

To clarify, if I have a dataset which can change, I can set up a chart so that:
1 the title comes from a cell on the sheet which reflects what I want it to say about the data displayed
2 the number of points displayed varies as the number of values in the data changes (using a dynamic named range)
3 The axis titles come from a cell as well.

But I'm stuck with Excel's choice of the axis min and max values -which is frequently a pretty bad choice. What I would prefer to do is calculate on the sheet some sensible min & max valus for the axis and assign them to the chart axis limits.

Now I already know how to do this in VBA - which is why I'm not posting the question there. But that would demand that I'd need to trigger the code using, say, the worksheet change event, which I see as a slightly messy solution.

The chart changes in 1 to 3 above could also be done using VBA, but I think that doing it directly from the sheet is a much neater solution for those items. So, is there any way of doing the same kind of thing for the axis limits?

Tony

 

[SkipVought]

hi,

The chart changes in 1 to 3 above could also be done using VBA, but I think that doing it directly from the sheet is a much neater solution for those items.

I suppose that you are using something like a Data > Validation -- LIST to make a selection for your chart, that filters your source data and changes a criteria in your chart title & axis formulas.

I often use this technique with a primary and secondary value axis that do not coincide with max & min axis values. VBA is the only means that I know of coordinating the axes and setting the AutoFilter criteria, the only pieces of code required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Thanks for the response Skip.

No, actually, I don't use the list method - in fact I've come across lists but never really got into using them.

To, for example, make the chart display just the right number of datapoints for the dataset in use, I'd name the ranges using a dynamic formula and assign the names to the series. Then to change the title, I might put some code into a cell which created the text I wanted, based on the data, say, including the column heading for example, then assign the output of that cell to the title. Is that clear? If not I'll provide a concrete example.

However, I think you've probably answered my question - if you don't know of a way of doing it without VBA then I'm perfectly prepared to believe there isn't one.

Tony