Dynamic chart not working with stacked column

**simonzxr** · 11-27-2012, 06:10 AM

Hi,
I've been working on creating a dynamic chart which works well and updates as required as a clustered column. However, the output I need is a stacked column. If there any way of making this dyanmic?

I've attached a simplified version. Basically, on tab 'inputs' the user will use drop down lists to select data from the data tab. I've disabled this for simplicity. Calculations are made on calcs1 and as more materials are added they will add to the chart on the left. What I need is the same to happen for the chart on the right.

I've tried making the chart data range dynamic but it just does this once and then reverts. I then tried to make this process using a macro button but it deosn't recognise a range change.

Any ideas? I'm tearing my hair out!

ECO_DESIGN_TOOL_test.xlsmThanks

**lmp** · 11-27-2012, 06:22 AM

Hello,

dynamic charts need a little more work. For an intro see http://peltiertech.com/Excel/Charts/Dynamics.html

You will need to define range names for each series and plug these into the chart as the data source.

**simonzxr** · 11-27-2012, 06:28 AM

I have already done this. Each series already has a range name. This works fine for clustered column but not stacked column. I wouldn't normally post a question but no amount of googling has given me an answer!

**lmp** · 11-27-2012, 06:44 AM

You only have two range names in the workbook. The stacked chart has five series. Each of these series need their own range name. You cannot assign a range name to the "Chart data range" of a stacked column chart. You need to assign range names to each individual series.

As you have observed, assigning the range name to the "data range" box only works once. Excel then resolves that into an absolute address that is no longer dynamic.

So, create five range names, and assign them to the five series.

**simonzxr** · 11-27-2012, 06:52 AM

but what happens when there's 6,7 or 8 series which have been added by the user? Is there a way of the chart automatically adding a series when it appears but ignoring a blank or '0' entry cell? This is my problem but I'm not sure I'm being clear

**lmp** · 11-27-2012, 07:31 AM

I read you. It's perfectly clear.

Just help me understand: Why do you want a column chart AND a stacked chart of the same data? If you want to highlight the part-to-whole relationship of the data, sort the data by the values and use just the clustered column chart. The stacked chart does not add any value. I realize that you have posted a simplified version of your data, and it may not be so simple. I'm just trying to push your buttons, determining if the stacked chart is required at all.

You could also use a pivot chart as an alternative.

**simonzxr** · 11-27-2012, 07:44 AM

Thanks for taking the time to understand.
I don't actually want the column chart....it is just an example of how I want the chart to operate dynamically. The reason i want the stacked chart is so you can easily see the combined total on the Y axis at the same time as the realtionship between each material. I would actually have two of these charts side by side in order to compare products. No other graph cuts it for that kind of comparison and is the standard way of presenting this kind of data in my industry.

I'm not sure how I could achieve this using a pivot?

**lmp** · 11-27-2012, 08:09 AM

The problem with the stacked chart is that it consists of several series. These cannot be created dynamically with range names.

Andy Pope has an approach at OZgrid that may be worth a look, but it requires VBA. http://www.ozgrid.com/forum/showthread.php?t=50006

Andy stops by here regularly, so he may catch this ...