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

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

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

Chrisso wrote:
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.

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

Hi,

You need to unravel the Formula property of the series.

You might find this recent blog By Jon useful.
http://peltiertech.com/WordPress/200...ries-formulas/

Cheers
Andy

Chrisso wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

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

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