Results 1 to 5 of 5

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

This is a discussion on Stacked column next to a clustered column for each month in a 12 month range? within the Excel Questions forums, part of the Question Forums category; Hi guys, I'm trying to create a stacked column chart next to a standard column but am having trouble. For ...

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Location
    Cardiff, Wales
    Posts
    245

    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
    245

    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,356

    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
    245

    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
    245

    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

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
  •  


DMCA.com