Change chart's data source by drop-down criteria

Hello,

Jon Peltier has a wealth of articles about dynamic charts at http://peltiertech.com/Excel/Charts/Dynamics.html

With regards to your file:

Drop the merged cells in row 3. Use "Center across selection" in the horizontal alignment options instead of merged cells. This will save you a lot of headaches further down the line.

To create a dynamic chart, you will need some kind of OFFSET() formula to define named ranges to feed the chart. The order of the data table is a bit irritating, though. Normally, I'd expect to see the months progress from left to right. In your data table, the most current month is on the left. What will happen the next month? Will you insert new columns for December? That will probably break formulas, unless you know how to use formulas that don't shift with the cells.

So, my advice is: create a table where the time line goes from left to right. Use dynamic ranges with Index or Offset that are calculated based on the date selected in E12.

And, finally, a stacked column chart is not the best chart to compare the values of a single data series. For example: is the purple for "Non-billable efforts" for IT higher or lower than the purple for AG? Comparisons like that are best done in a panel chart. Again, Jon Peltier's site has very good articles and tutorials about those.

Have a read.

Relax.

Take some time to digest it.

Then come back and we'll work out how to apply it to your data.

cheers,

and with regards to this:

I can't access the excel sheet (using excel 2003) which makes it difficult to relate to the solution.

The compatibility pack is a free download from Microsoft, which enables viewing XL 2007 and later workbooks with XL 2003.