Excel refuses to show me what DataSource a chart is using. How to find this?

jombi

New Member
Joined
Apr 20, 2010
Messages
28
Hi all,
I have read that there is no way within VBA to determine a datasource of a chart. In lieu of that I would like to at least see the range that the chart is using by looking at the chart design in Excel (not VBA).

But what I see instead is a Blank where the Range should be, and a message to the effect "...the range is to complicated to display".

So my quandry is that I've been given some workbooks with charts that need fixing. However, I can't see what's wrong with the charts if I can't see where the data comes from. The quandry is that the charts "mostly work", and are reasonably accurate, but need tweaking. If I so much as touch the Range in the Series properties, I'm afraid I'll wipe out everything--and I rather have a "mostly working" sheet than one that's completely dead.


I have been given no direction as to what the chart should be--that would've been nice. My job was to 'analyze what's there' and fix it. But, like I said, I can't really analyze what's there because, while I can look at the chart results, I can't look at what the source was.

Is this info gone forever (except maybe in the original workbook creator's mind...who's not with the company any more)?
Thanks,
Jombi
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
if you send the spreadsheet to me I will work on it - PM your Email and I will contact you. I am retired so have the time for this.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Select just one of the series on the chart, then look at the formula bar, you may see a formula like:
=SERIES(...
which will give you a clue:
A SERIES formula is comprised of four arguments:

  • Series Name. Can be a cell reference or a literal string. Optional.
  • XValues. Can be a range reference (including a non-contiguous range reference) or an array. Optional.
  • Values. Can be a range reference (including a non-contiguous range reference) or an array.
  • Plot Order. Must be an integer.
Above from John Walkenbach: Excel Developer Tip: A Class Module to Manipulate a Chart SERIES
If you Google for:
"using vba to determine the ranges used in a chart"
including the quotation marks, you should find some books by John Walkenbach where he discusses this very problem.
 

jombi

New Member
Joined
Apr 20, 2010
Messages
28
p45Cal,
Thanks very much, that did it!

oldbrewer,
Thanks for the offer, I think p45's answer will get me going though. If I get stuck I may contact you,
--Jombi
 

jombi

New Member
Joined
Apr 20, 2010
Messages
28

ADVERTISEMENT

P45Cal,
I have a follow up curiosity--when I do as you suggested--Selecting the series, I see the that entire Series() formula just fine. However, if I make one minor change (such as changing the series name), then it disappears from the formula bar and I can no longer select it (!?). I can select other points, and their Series() formulas show up fine. But once I touch one--it's now gone. Have you experienced that? What I have to do is fully close and re-open the sheet in order to re-edit the Series() function.
Do you know what might cause that, or how to get around it?
Thanks,
Jombi
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
  1. What version of Excel?
  2. Is it a very long formula?
  3. Is there any code/macro in the workbook?
 

jombi

New Member
Joined
Apr 20, 2010
Messages
28

ADVERTISEMENT

  1. What version of Excel?
  2. Is it a very long formula?
  3. Is there any code/macro in the workbook?
p45Cal,
My Excel version is 2007 but the sheet is an .xls created in ver. 2003.
No, the formula for the Series just has those 4 arguments and they are fairly simple. The longest part is the filename used as part of the range in the formula--no path, just the filename but it has a timestamp as part of the name. I'd say the whole formula is under 80 characters.
Yes, there is code in the workbook, quite a bit, and some of the code does use the data in the series to do certain things.

Thanks for your further assistance in this. I'm just now getting the reference of your username--I had at first thought it was a gun reference, then I saw it on a smaller screen and it hit me.
--Jombi
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
I'm struggling to find why it may be doing that.
Is the chart a chart on its own chartsheet, or is it embedded in a normal sheet?
If it's on its own chartsheet then right-click the sheet's tab and choose View code, is there any code there? If so what is it?
 

jombi

New Member
Joined
Apr 20, 2010
Messages
28
p45cal,
The chart is on a separate sheet, right-clicking the sheet tab show's no code (I click the lower left corner icon of the vba module for "Full Module View" to verify).

However...there is an event handler added in the Workbook_Open code. What it does is add a context menu handler to pop-up a button which brings up a form to add an annotation to the chart.

Yet...I have tried with this code disabled, both by holding shift down and also...to be extra sure...commenting out all of the _Open code and any context-menu creation code, even putting msgboxes in the code so that if it were to run I'd know it.

So yes, there's code, but I've disabled it and I still get this behavior where once I edit a series() formula it becomes hidden. Also, it turns out the only thing in the series() formula I can edit is the Name. When I try to edit a Range it lets me change the number, for example, but clicking the Formula Checkbox does nothing, hitting Enter does nothing--the cursor just stays where I've made the edit and the only way out is ESC.

Baffling.

Here is an example of a series formula:
=SERIES("SalesK",Data!$A$15:$A$36,Sales_201304221608008313.xls!K_Range,2)
"Data" is the first sheet, containg the data.
K_S8 is a range referring to: OFFSET(Data!$K$15,0,0,COUNT(Data!$A:$A),1)
Column A has the x-axis date range, column K has sales of a certain category.
Actual data starts at row 15, the rest is misc. header data.


There are graphs for most of the columns (B and on) with very similar series, and they all act this way.
Thanks,
Jombi
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
I've been trying to reproduce this with defined dynamic ranges in another workbook, but I can't.
Would you be willing for me to have a look at this with a remote viewer such as TeamViewer?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top