Dynamic Scales on Graph 1

[MooSki]

Hi guys,

Please bear with me, there is some background to this problem.

I produce reports for approx 30 sales people. The main one is a waterfall graph to show how their sales portfolio is doing financially. For those that haven't come across them before, I have linked to them before in a previous thread of mine and I find them very useful.

This particular one shows:

Prev Month total -> New sales in -> Existing business increase/decrease -> Current month total

So you start with the PM total, add the sales in, add (or subtract) the existing business and you end up with the CM total.

The main thing to consider here is that different sales people have different sized portfolios, some have very slow moving, large PFs, some have very rapidly expanding ones, so the numbers involved here are very different.

When producing these graphs in the past, I created one Excel file per salesperson, so with this, I could manually adjust the scale on the graph to suit the start and end figures, making the items in betwen much more readable. If you imagine a start of 1,000,000, new sales of 100,000, existing of -50,000 leaving a CM total of 1,050,000 then you can see that if the scale started at 0, the two outer columns would be very tall and the two inner columns very squashed, so I used to adjust the scale "minimum" to approx 900,000 to make the two middle columns more readable.

Now, I have been asked to produce an all-in-one report, where based upon drop down lists, you can choose the salesperson you want and the table and graph (based on hidden data on another sheet) are updated. I have got this far, but my problem is how "readable" the columns are.

Q. Is there a way of changing the scale on the graph depending on the data going into it, preferably the value of the Prev Month Total?

I suspect that some VB coding will have to be done, but I was hoping to avoid that.

My apologies for the long-windedness of this post, I just wanted you to have some background on what I was doing here.

Many thanks in advance,

Mooski.

 

[SkipVought]

Hi,

YES, it will take code. Consequently, you would be better served in VBA Visual Basic for Applications (Microsoft) Forum707. Please post your question there as it will require coding.

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[MooSki]

Skip,

Thanks for the swift response, I shall do that.

Mooski.

 

[SkipVought]

I may not have a chance to respond to your post in forum707, but here's a sample of some code that I am currently using that is triggered by a Spinner Change event. It changes the MajorUnit property of the Value Axis object...

Sub Spinner1_Change()
    Dim iMajorUnit As Integer
    wsLoanData.[a1].CurrentRegion.AutoFilter _
        Field:=7, Criteria1:=wsLoanData.[PageVal] - 1
    With ActiveChart
        With .Axes(xlValue)
            [MinVal] = .MinimumScale
            [MaxVal] = .MaximumScale
            Select Case [MaxVal] - [MinVal]
                Case Is > 360 * 4
                    iMajorUnit = 180
                Case Is > 360 * 3
                    iMajorUnit = 90
                Case Is > 360 * 2
                    iMajorUnit = 60
                Case Is > 360
                    iMajorUnit = 30
                Case Is > 180
                    iMajorUnit = 14
                Case Else
                    iMajorUnit = 7
            End Select
            .MajorUnit = iMajorUnit
        End With
        With .Shapes("lblPage").OLEFormat.Object
            .Caption = "Page " & wsLoanData.[PageVal]
        End With
    End With
End Sub

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would cross the road for 2 cents, is…
Poultry in motion for a paltry amount! [/sub]

 

[MooSki]

Skip,

Excellent stuff there, that's a great help.