How can I ask a chart where its data comes from? Chrisso <chris75sutton[ at ]gmail.com> 12/19/2008 10:57:16 PM 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

Re: How can I ask a chart where its data comes from? smartin <smartin108[ at ]gmail.com> 12/19/2008 11:40:11 PM Chrisso wrote: [Quoted Text] > 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.

Re: How can I ask a chart where its data comes from? Andy Pope <andy[ at ]andypope.info> 12/20/2008 12:45:48 PM 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 wrote: [Quoted Text] > 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

Re: How can I ask a chart where its data comes from? Chrisso <chris75sutton[ at ]gmail.com> 12/31/2008 10:29:15 AM 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