Can you create dynamic named series for a chart?

Question

I need a column chart which trends 6 month(I have this covered) but stretches rows based on values in the data set. I could hook the chart to the pivot BUT I need to summarize all values over a count of 5.

so, if you have 4 values, then just who 4 (3,2,1) BUT if you have 12 values then show the top 4 (I can do this) but sum the other 8 (I can do this)

the problem is: How do I get the chart to add and remove series based on this.

Right now I have an area that does all this calculation but I don't know how to get the chart to use the dynamic range for it's data (and dynamic series)

Answer 1

While I am not completely clear on your question there are a number of sites that explain well the process of using dynamic range names as inputs to chart series - the key issue which is easy to forget is that when adding a dynamic range name as a charte series, that this reference must include the worksheet name

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

Peltier http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html