Results 1 to 3 of 3

Trouble with a Dynamic Clustered Stacked Column Graph in Excel 2007

This is a discussion on Trouble with a Dynamic Clustered Stacked Column Graph in Excel 2007 within the Excel Questions forums, part of the Question Forums category; Hi All, I'm looking for some help with a dynamic clustered stacked column graph. I've been using the fantastic guide ...

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    11

    Default Trouble with a Dynamic Clustered Stacked Column Graph in Excel 2007

    Hi All,

    I'm looking for some help with a dynamic clustered stacked column graph. I've been using the fantastic guide provided by Jon Peltier http://peltiertech.com/WordPress/clu...mn-bar-charts/ which has gotten me 1/2 way to my goal, but I can't get the chart to work dynamically (it's missing out some of the series in the later fortnights..

    I'm designing the spreadsheet for use by people not particularly proficient in excel, so it's important that they can simply key in the data and the chart then works dynamically to change (InputData tab).

    I've currently got 10 series (of which 6 are displayed below due to space) which shouldn't change over time, but each passing fortnight new data will need to be entered for the 10 series. The chart needs to display the previous 6 or 12 fortnights worth of data which I'm using the ChtLen1 named range. The other named ranges use OFFSET formulas

    Any help you could offer would be appreciated, I've got a number of other charts to work dynamically but only where there is one entry for the dates on the left hand side i.e 21/2011 them 23/2011 on the following row

    Thanks

    David


    Input Data Tab

    Length 40
    Bought and Sold

    Week Type A Bought Type A Sold Type B Bought Type B Sold Type C Bought Type C Sold
    21/2011 10 16
    21/2011 23 8
    21/2011 4 8

    22/2011 10 15
    22/2011 10 9
    22/2011 5 9

    23/2011 10 9
    23/2011 5 9
    23/2011 5 9

    I'm using dynamic named ranges for the the series along the top (Type A Bought week 21/2011 =OFFSET(chtCats1,0,1)

    The formula for ChCats1 is
    =OFFSET(InputData!$A$7,COUNTA(InputData!$A:$A)-2,0,-MIN(chtLen1,COUNTA(InputData!$A:$A)-2),1)

    And the formula =InputData!$B$4 for ChLen1





  2. #2
    New Member
    Join Date
    May 2013
    Posts
    11

    Default Re: Trouble with a Dynamic Clustered Stacked Column Graph in Excel 2007

    Week Type A Bought Type A Sold Type B Bought Type B Sold Type C Bought Type C Sold
    21/2011 10 16
    21/2011 23 8
    21/2011 4 8
    22/2011 10 18
    22/2011 20 15
    22/2011 6 10
    23/2011 12 18
    23/2011 23 18
    23/2011 10 8


    Just realised that the table didn't insert properly, this is how the input sheet looks.

    Thanks

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    26

    Default Re: Trouble with a Dynamic Clustered Stacked Column Graph in Excel 2007

    Hi DaveBre,

    I've exactly the same issue that you have, did you ever figure this out?

    Cheers,

    Tom

Tags for this Thread

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