Waterfall Chart - Dynamic Axis Start

[EasterGreen]

I have a waterfall chart in a report that has different departments' data fed in to it and each has significantly different spends.
For larger departments the total "start" and "end" columns are quite tall and force the chart axis to be large numbers.
Any minor bars showing small movements between start and end are hard to interpret.
Is there any way to dynamically change the Y axis minimum from zero to x such that I can set a formula to define the start point?
Image 1 is the automatic axis, Image 2 is where I have manually altered the axis start point.

 

[RoryA]

You could use a function like this:

Function SetMin(MinValue As Double, ChartName As String)
    Application.Caller.Worksheet.ChartObjects(ChartName).Chart.Axes(xlValue).MinimumScale = MinValue
    SetMin = "Set"
End Function

and then call it from a cell on the same sheet as the chart (you'll need to alter it if it's a chart sheet) passing the min value and chart name.

 

[EasterGreen]

Thanks for your reply. I'm afraid I don't follow it.
My chart is "Chart1" and the minimum value could be in cell D14.
And not sure what call the cell means.

 

[RoryA]

I mean you'd add that code to a normal module in your workbook, then in a cell enter;

=SetMin(D14, "Chart 1")

for example. You could also use an event Like Worksheet_Change and just monitor the cell(s) that you use for the relevant chart values.

 

[EasterGreen]

I really appreciate the advice but I can't get this to work.
I have entered:
Function SetMin(MinValue As Double, ChartName As String)
Application.Caller.Worksheet.ChartObjects(Chart1).Chart.Axes(xlValue).MinimumScale = MinValue
SetMin = "Set"
End Function

and then in my worksheet typed =SetMin(D14,"Chart1") in cell D15
I get #Value! as the result in D15 and the chart doesn't change.

 

[EasterGreen]

Update - I was using "Chart1" and not "Chart<space>1".
It now works, thanks!