Waterfall Chart - Min & Max Scale Based On Cells

[GPerky]

Hi Everyone,

Firstly, I would like to set the scale on my Waterfall chart so it maximises the length of the bars - the Auto option on the Waterfall doesn't seem to do this very well.

Secondly, all my data changes based on a drop down for month, so I have a calculation to determine the min & max values.

I have then setup the VBA below on the sheet with the waterfall graph to trigger the change whenever cell B1 (the month) changes, however the scale doesn't seem to change??

Any help much appreciated.

Thanks

Graham

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$b$1" Then

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("b19")
.MaximumScale = Range("b20")
End With

End If

End Sub

 

[Jeffrey Mahoney]

Go look at my example workbook for the waterfall chart. It has a formula in the title and gets updated when I change the cell it references. If your chart is not updating then I suspect you don't have automatic calculations on.

 

[Jon Peltier]

The trick @Jeffrey Mahoney describes does not work in a waterfall chart, or any of the other new charts added in the past couple of years. These charts use a new charting engine that has some great capabilities but which also is not up to speed on some of the regular features of Excel charts.

 

[Jeffrey Mahoney]

I disagree with you Jon. I opened my waterfall chart workbook and it does have a formula in the chart title that links to a cell.

 

[Jon Peltier]

I disagree with you Jon. I opened my waterfall chart workbook and it does have a formula in the chart title that links to a cell.

Before my earlier reply, and again just now, I built an Excel Waterfall chart (not a stacked column chart formatted to look like a waterfall), and I could not link the chart title to a cell. I've done this thousands of times over the years, so I know the protocol.

Did you do this with a native Excel waterfall, or with a handmade one?

 

[Jon Peltier]

@Jeffrey Mahoney , I just went back and saw your link to your waterfall article. That is not a "native" Excel waterfall, it's a stacked column chart. Being a "regular" Excel chart, your waterfall can have a chart title that links to a worksheet cell. @GPerky 's chart is a native waterfall, so it is deficient is several ways, including linking of text elements to cells.

 

[Jeffrey Mahoney]

I was just ready to reply. Yup, it's a column chart. I don't think native waterfall charts were available when I needed it.