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

 

[smartin]

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,

Hi Chrisso,

In Excel 2003 and prior, right click the chart area and inspect "Source
Data". Not sure if this is the same in 2007.

 

[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