Charting from a Pivot table, losing series values. (named range possible?)

[Grizlore]

Hi All,

Is there a way to use a sort of names range, to chart from a pivot?

I currently have an ‘analysis’ worksheet which displays two pieces of data from two pivot tables, which are on different worksheets.

The ‘analysis’ worksheet displays the last months figure, and charts the history

To get the last month’s figure of any given metric, I use something like...

 =GETPIVOTDATA("[Measures].[Attendance]",LastMonthData!$A$7)

This works fine, as however the pivot table changes, the GETPIVOTDATA function finds it, wherever it is located in the pivot table.

When I come to chart data, I have a problem.

This is because the data for the chart is in a pivot table, but I link to it in a “normal” way, and however the pivot table changes, the data is lost.

The series value for the chart is like this...

 =ScorecardData!$B$8:$N$8

As you will see this is an absolute reference, and if the pivot table is changed, then I lose my data.

Is there a way to use a sort of names range, which finds then name (eg Attendance) and charts the 13 number sitting to the right of it, in a similar way to GETPIVOTDATA?


Any help would be greatly appreciated.

Regards

 

[Grizlore]

anyone know if you can link to a pivot chart, from a pivot table with and linked series, which won't disconnect when you sort the table