Trouble with a Dynamic Clustered Stacked Column Graph in Excel 2007

DaveBre

New Member
Joined
May 4, 2013
Messages
11
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





 

DaveBre

New Member
Joined
May 4, 2013
Messages
11
WeekType A BoughtType A SoldType B BoughtType B SoldType C BoughtType C Sold
21/20111016
21/2011238
21/201148
22/20111018
22/20112015
22/2011610
23/20111218
23/20112318
23/2011108

<TBODY>
</TBODY>


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

Thanks
 

ShelleyBelly

New Member
Joined
Mar 2, 2011
Messages
44
Hi DaveBre,

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

Cheers,

Tom
 

Forum statistics

Threads
1,081,832
Messages
5,361,586
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top