Excel Dashboard Training

Comment from XL-Dennis
Time: Tuesday, April 20, 2010, 1:07 pm

Jon,

I noticed You have the topic “Data quality” which I think is very good. Just to satisfy my curiosity about it; could You have the kindess to elaborate it?

Thanks,
Dennis

Comment from CKZ
Time: Tuesday, April 27, 2010, 10:37 am

Hi Jon,

I read the following article – very informative, thank you.
Link Chart Axis Scale Parameters to Values in Cells.

Read more: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html#ixzz0mJajIXkT

You say that excel automatically updates the scale based on what it thinks the scale should be. I am working in a pretty involved worksheet that pulls data to specific worksheets that would result in new graphs. The scales are not updating when new data replaces the old data. The code creates to graphs – a preview graph which is a graph on the same page as the data and a chart object as the presentation graph. It is the chart object that is not updating. The chart within the same page as the data works fine.

Any suggestions on how to reset the scale so it can automate on its own?

In the future I will attempt to link and do as you described above, but for now, I just want it to update.

thank you,
cassie

Comment from CKZ
Time: Wednesday, April 28, 2010, 9:25 am

The normal Excel update of the axis scales does not work, so I tried to make it code-driven for the scales to work.

The code I inserted is as follows:

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

Is this correct?

Comment from Martín
Time: Wednesday, April 28, 2010, 1:00 pm

Jon,

I was really interested in participate on the training, however costs do not allow me to do so (travel from Argentina to US). would there be a remote training chance?

Rgds.

Comment from CKZ
Time: Wednesday, April 28, 2010, 2:30 pm

A button triggers it.

The button takes data from one spreadsheet and puts it into a second spreadsheet. The code moves the data and formats the graph to be the correct colors based on the data that was put into the spreadsheet.

That code I showed you earlier was labeled as “scale” so I thought that it was updating.

Obviously I am an amateur that just fumbles in VBA with trial and error.

The data that can be called into this one spreadsheet can have a wide variety of scales.

Is there code I can add to make sure it updates the Chart Object? Why would the chart object and the chart within the spreadsheet act differently? The code I showed you earlier is not used for the graph within the spreadsheet.

Comment from CKZ
Time: Wednesday, April 28, 2010, 2:33 pm

One more thing to add – the ultimate goal would be to tell the chart to always have five gridlines, but adjust scale accordingly when the data changes.

Comment from CKZ
Time: Thursday, April 29, 2010, 11:08 am

Jon,

I decided to give your code a try to tell the program to scale the y axis only based on two cell references.

This is the code:
With ActiveChart.Axes(xlValue)
.MaximumScale = Worksheets(“Preview”).Range(“$L$4″).Value
.MajorUnit = Worksheets(“Preview”).Range(“$L$5″).Value
End With
(It Works Great!- Thank you)

My goal is to always have 5 gridlines. My challenge is that the data is always a different scale in size, for example, sometimes the data will be in 100,000s other times it is in the millions.

So, in my Maximum Scale Reference, I created a formula –
$L$4 = CEILING((MAX(D29:L40)),25000)
D29:L40 – the cell reference range
25,000 – is an arbitrary number I chose for the significance

The Major Unit Reference Cell formula for $L$5 = $L$4/5

Now my question – do you know of any better formula within excel to look at the range of data and give a nice rounded number? Having 25000 as my significance for the Ceiling formula isn’t the solution because the significance can change as the data changes to thousands, hundreds of thousands, and millions.

I appreciate you help.

Comment from Matt
Time: Friday, May 7, 2010, 4:37 pm

Hi Jon
This is off topic – apologies – but I have come across an interesting formatting situation you and/or readers may be interested in commenting on.

I have to produce a report in Word that contains Excel charts. All pretty standard. Tricky bit is they want one Word file that can:

Be read on screen in Word or .pdf format – so, color chart bars and legends are OK;

AND the same file can be printed in B&W – so, colors have to be such that they differentiate in B&W

AND they want to be able to copy/paste the charts from the Word doc to a Powerpoint presentation – again, colors are ok, on a huge scale.

My solution is to use colors that print in differing shades of grey-scale e.g. light blue, mid red, dark green. It results in some hideous combinations.

But perhaps there is a smarter way?

Cheers
Matt