Thread: Stacked clustered column_dynamic charting

Hi,

I have attached a file where the information is given month wise. I would like to show the given information in stacked clustered column through dynamic chart. The drop down menu displays the information, but some how the chart does not update as I switch through the options.

Any help will be much appreciated.

Thanks in advance

Hello rez,

I'm not sure I understand what you are trying to show in the chart. The January numbers a hours, the Feb values are percentages, March figures do not have a unit and April is days. What are you trying to show? Are these values just random dummy numbers?

The selection box lists Hours, Work, Rest, Days. What data would you like to show when any of these options are selected? Once you've sorted that out, you need to create a table where you put your values to be charted, and based on the selection in the combobox, populate that table from data you have elsewhere in the worksheet. Just inserting a combo box does not do it automatically.

Hop on to Jon Peltier's site and read up on how to create dynamic charts here: http://peltiertech.com/Excel/Charts/Dynamics.html

hth

Hi teylyn,

I wanted to have a graph where you can view the information for Hours, Work, Rest, Days for FT and CT month wise. But I donot want to see hours, work, rest and days information together but through selection from the control tool box.

I was able do this in column chart for FT only through offset functions, but I don't know how to combine two sets of data (FT and CT) in stacked clustered chart.

Any help???

Hi rez, please do not quote whole posts. It's just clutter.

Where is your data for Hours, Work, Rest, Days? I see data for Day, Night, Afternoon, categorised by month and subcategorised by FT and CT.

Also each month seems to have a different unit of measure, as I already listed above. I think you need to step back and organise your data table to include the values you want to chart. Then we can take it to the next level of dynamic charts.

Manually select what you want to chart for "Hours" for example. Where is the Hours data for Jan, Feb, Mar, etc?? Where is the Rest data for Jan, Feb, Mar, etc.

And the same for the other two criteria in your select box.

Do you see where the gaps are?

Ah, that's much clearer.

There are several ways to achieve what you want. In the attached file, I've copied the Hours worked table below the Days worked table. Then I've created range names DaysWorked =C20:E27 and HoursWorked = C31:E38

The uppermost table is the basis for the chart. It is populated based on the value that is selected in the list box. If the value is 1, then HoursWorked will be copied into the chart table, otherwise, DaysWorked will be copied.

This is done with an arrray formula. I selected C9:E16, entered the formula

=IF(B8=1,HoursWorked,DaysWorked)

and confirmed with Ctrl-Shift-Enter.

Now, whenever you change the selection in the list box, the data table will receive different values and the chart will change.

This is just one of many ways to achieve what you want. You need to get familiar with range names and have a read of the link I posted above.

cheers

... which brings us to one of the other many ways to do this. The above method is not the best in this case, since the unit of measure changes with the selected value from the list box. Unit of measure or number formatting can not be influenced with conditional format, so whatever the format of the data table is, will show up on the chart, even if the unit of measure changes. So, that's not what we want.

On to the next approach.

Instead of defining a fixed area for the to chart and populating that area with numbers based on a selection in the list box, the attached file takes a different approach.

The chart series are defined with range names AfternoonShift, DayShift, NightShift. The definitions for these range names are created with an OFFSET formula that shifts the reference for the range names depending on the value selected in the list box.

Study the range names ChartLabels, and the three names above.
Note how I changed the positioning of the data to make it easier to use an Offset.

When you click Insert - Name - Define, then select one of the range names and click in the Refers to box, you'll see the marching ants around the range. Change the selection in the list box and repeat the process and you'll see that now another range answers to that range name.

Let me know how you get on.

hth