How can I ask a chart where its data comes from?

[Chrisso]

Hi there,

How can I ask a chart where its data comes from? Ideally I would like
to get the range back that is represented by a point on the chart. I
at least need to know which worksheet the data is on.

Is this possible? I cannot see how using Excel VB help.

Cheers for any ideas,
Chrisso

 

[Andy Pope]

Hi,

You need to unravel the Formula property of the series.

You might find this recent blog By Jon useful.
http://peltiertech.com/WordPress/2008/12/17/how-to-edit-series-formulas/

Cheers
Andy

 

[Chrisso]

Thanks Andy.

I came up with this grubby but effective (so far) code:

Dim sFormulae As String
sFormulae = chtEvent.SeriesCollection(1).FormulaR1C1
' worksheets can appear in the formulae in two forms:
' "=SERIES(Data!R1C2,Data!R2C1:R10C1,Data!R2C2:R10C2)
' "=SERIES('My Data'!R1C2,Data!R2C1:R10C1,Data!R2C2:R10C2)

If InStr(sFormulae, "=SERIES('") > 0 Then
' sheet name is enclosed in single quotes:
Debug.Print "[" & Mid$(sFormulae, Len("=SERIES('") + 1, InStr
(sFormulae, "'!") - Len("=SERIES('") - 1) & "]"
Else
' sheet name is NOT enclosed in single quotes:
Debug.Print "[" & Mid$(sFormulae, Len("=SERIES(") + 1, InStr
(sFormulae, "!") - Len("=SERIES(") - 1) & "]"
End If


Chrisso