Can you chart a series with GetPivotData (or something like?)

[Grizlore]

Can you chart with GetPivotData (or something like?)

I have a sheet with numerous charts, plotting data from a pivot.
The chart are using series values like this

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

This is fine, however when the pivot changes, I lose the references and everything gets mixed up.

Is there a way to use a sort of names range to chart from a pivot? So the chart plots the data in relation to the name?
Getting a single number works with this, but I can’t work out how to plot a series

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

Any help please?

 

[cornflakegirl]

Could you not just use a pivotchart?

 

[Grizlore]

No, I have one large pivot table containing dozens of values, spanning 13mths and off that dozens of charts plotting individual items.

Once I use a pivot chart it'll change the pivot

 

[GlennUK]

Have another pivot off the data, just for that chart.

 

[Grizlore]

But I would need several dozen of pivots, one for each chart. Which would be a bigger nightmare.

Do all charts series need to be reference like this?

=LastMonthData!$A$13:$C$13

Surely there much be a way to use a lookup?

 

[cornflakegirl]

Could you do it with INDEX and MATCH? Use GetPivotData for the find argument in MATCH, and pull the whole row in INDEX?

 

[Grizlore]

Could you do it with INDEX and MATCH? Use GetPivotData for the find argument in MATCH, and pull the whole row in INDEX?

Ummm... I don't know.

When adding a series into a chart, it doenst seem to like formulae. Can it?

I need to pick up a 13 month range (obviously 13 numbers) and chart it, so I'll have to look into how I could do that with your suggestion

 

[GlennUK]

Can you retrieve all 13 values by using 13 cells with GETPIVOTDATA? So, you make a table for charting from?

 

[Grizlore]

Can you retrieve all 13 values by using 13 cells with GETPIVOTDATA? So, you make a table for charting from?

I was thinking that might have to be the solution, although I was going to use VLOOKUP, as I am not sure how to capture each of the 13 values with GetPivotData.

VLOOKUP is easiest for this (? no?)

This does seem an un-elegant solution though, having to create another table off of a pivot table.

I would have thought that this was a common problem, and would have an elegant solution