Can you create dynamic named series for a chart?

[chrisseanhayes]

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)

 

[Excelestial]

Create dynamic series by creating dynamic lists, naming them using the offset function. Once the name has been created, use the name when you create the chart and add the series. Edit the series to include the name of the dynamic list you created. There is a naming convention you must use--the name of the list MUST be included AFTER the filename and exclamation mark. An example is:

='Board Reports MASTER-AUG2011 - Excelver07.xlsm'!Ret6moVal

 

[chrisseanhayes]

@Excelestial, I did this and it doesn't work. The chart will accept the named range for creating the chart but it converts it to a un-named range and always looks for the original rows and columns that were selected by the named range at that time and point.

 

[Excelestial]

How is your dynamic range defined? Can you show the formula you're using to make the list dynamic?

 

[chrisseanhayes]

=offset(start,0,0,if(count(totalcolumninpivot)>5,5,count(totalcolumnsinpivot)),6)
I'm creating a top 4 or 5 where row 5 is "Other" summed based off a pivot that shows all the values. I use the pivot to calculate how many rows I need. All other calculations are done in the referenced area.

 

[Excelestial]

My experience has been to never rely on the non-existent dependability of using formulas against a pivot table. I'm sure the pivot table is resetting your array.

 

[chrisseanhayes]

yes, it's resetting my array, because it's a 'dynamic' area that get's filtered by geography. That's the whole point. I need a 'dynamic' data set for the chart with 'dynamic' series that change as the report is filtered by geography