Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Stacked column next to a clustered column for each month in a 12 month range?

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Stacked column next to a clustered column for each month in a 12 month range?

    Hi guys,

    I'm trying to create a stacked column chart next to a standard column but am having trouble.

    For the months Jan to Dec, I want to see s stacked column showing:

    Sales 1
    Sales 2
    Sales 3

    So Sales 1 on top of 2 on top of 3

    However, next to it I'd like to show a single column for Losses.

    I can get it working if I change the chart type of Losses to a line, scatter etc, but I'd really like a comparable column. I can sort of get something along the right lines, by using two columns for each month, Sales in one, Losses in the other, but if I try to change the chart type on one it chnages it for all of them.

    If I put Losses on the secondary axis then it seems to work, but I need to make sure that the primary and secondary axis are identical. I've tried doing this with some code that I found but it doesn't appear to work...

    Code:
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Select Case Target.Address
        Case "$BQ$3"
            ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlCategory) _
                .MaximumScale = Target.Value
        Case "$BQ$4"
            ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlCategory) _
                .MinimumScale = Target.Value
        Case "$BQ$5"
            ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlCategory) _
                .MajorUnit = Target.Value
        Case "$BR$3"
            ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlValue) _
                .MaximumScale = Target.Value
        Case "$BR$4"
            ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlValue) _
                .MinimumScale = Target.Value
        Case "$BR$5"
            ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlValue) _
                .MajorUnit = Target.Value
        Case Else
    End Select
    
    End Sub
    Is there an obvious solution? Am I on the right tracks, but cocked up the code?

  2. #2
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stacked column next to a clustered column for each month in a 12 month range?

    Okay, I've managed to get the two seperate columns using a primary and secondary axis. However, I'm no closer to getting the code to match the maximum axis values to work:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Select Case Target.Address
        
        Case "$BQ$3"
            ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlValue) _
                .MaximumScale = Target.Value
        Case "$BQ$4"
            ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlValue) _
                .MinimumScale = Target.Value
        Case "$BQ$5"
            ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlValue) _
                .MajorUnit = Target.Value
            
        Case "$BR$3"
            ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlSecondary) _
                .MaximumScale = Target.Value
        Case "$BR$4"
            ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlSecondary) _
                .MinimumScale = Target.Value
        Case "$BR$5"
            ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlSecondary) _
                .MajorUnit = Target.Value
        Case Else
        
    End Select
    
    End Sub
    I've no idea if Chart.Axes(xlSecondary) is the correct syntax for the secondary y axis...

  3. #3
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,596
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Stacked column next to a clustered column for each month in a 12 month range?

    Read my tutorial about Clustered-Stacked Column Charts. Hint: one of your stacks has 3 series, the other has 1.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  4. #4
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stacked column next to a clustered column for each month in a 12 month range?

    Aha, that was the page that I used to get it sort of working! I've just noticed that you've managed to get both sets of charts working off the same axis, I'll have to have a good read

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stacked column next to a clustered column for each month in a 12 month range?

    All sorted now John, much obliged for the tutorial

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •