Comment from Peter H
Time: Thursday, September 10, 2009, 6:59 pm

Jon
I believe there is an easier method to construct a dynamic chart than using the Offset Formula. ( I have mentioned it a couple of times on the Pointy Haired Dilbert Site). It works in 07 but not sure about earlier versions)
Using your example:-
Simply highlight the data (A1:E7), Insert Table then Insert again and select Chart type (in this case Line) and it is done.
To add data simply enter it in the next line (Row 8).
More series can also be added simply by adding the data to column F and the chart will insert it automatically.

Are there any disadvantages to this method?

Peter

Comment from Andy Holaday
Time: Thursday, September 10, 2009, 8:06 pm

Hi Jon,

Charts with dynamic ranges are cool.

In a related vein, I’m wondering if you have done much in the way of interactive charts? Using form controls, I have put together just a couple of these. Most recently I released one that uses checkboxes that control hiding/showing different series. It is a big hit with the users.

Thanks as always for your great tips.

Comment from jeff weir
Time: Friday, September 11, 2009, 6:16 am

Hi Jon. Another great tutorial. The way you use a simplified offset to quickly relate the ’slave series’ (i.e the y series) to the master series (the x series) is a nifty shortcut.

It’s worth pointing out though that if you delete columns between the master series and the slave series at some point in the future, then your offsets will all need to be adjusted to compensate. This might be the case if you’re working on a dashboard, and are still finalising what data you will plot.

In this case, it’s probably safer to use the same method as you used for the x series when defining each of the y series – that is, base the offset reference from the specific column in which the data resides. That way, no matter where the column gets moved around the worksheet, the function will still point to the data you want.

Comment from jeff weir
Time: Friday, September 11, 2009, 10:09 am

That is such a simple, effective work around that I can’t believe I didn’t think of it! After all, I use the ROWS function all the time in formulas for this very reason…why didn’t I think of using the COLUMNS function!

It’s funny…sometimes when we use Excel’s functions I guess we can concentrate too much on fufilling the parameters of those functions, rather than thinking about the problem.

How does the saying go…you learn a new trick from an old dog every day?

Thanks Jon

Comment from TV
Time: Monday, September 14, 2009, 9:35 am

If I understand your formulas, this means that you can’t put additional data in column A below the data table? The COUNTA function on column A won’t work like a CTRL+SHIFT+DOWN selection would, which would ignore any non-contiguous regions. If you insert text below the table in column A, it will count that number of rows and append it to the end of the series, right?

Comment from Peter H
Time: Monday, September 14, 2009, 6:48 pm

TV – Using the technique of using a Data Table (or List in 03) will overcome this problem as long as you leave a blank row and column around the Table.
You can have several Data Tables on the one worksheet to generate several different Charts

Comment from Gene
Time: Wednesday, September 23, 2009, 10:39 am

First, thanks for all your help…I bought you a cup of coffee this morning…I suspect I will be buying more…

Is there anyway to avoid putting the ‘workbookname.xls’ in the charts series definitions. I am creating templates for my customers to use, and they can rename them to anything…

Thanks again…

Comment from Gene
Time: Wednesday, September 23, 2009, 11:02 am

NEVERMIND !!!

I posted my question to soon…never thought to try a “Save As” to see what would happen…even renamed the workbook, and excel still managed to change the workbook name reference…

Smarter than I am I guess…

Thanks again

Comment from Dan Pitcock
Time: Tuesday, October 6, 2009, 11:13 am

Using your Panel Chart tutorials to build stock charts. Awesome tutorial! Using error bar process for variable height panels. After using the paste special process, when I double click the series and and try to align to primary or secondary axis, the line won’t go exactly vertical in line with the axis; therefore, the line won’t extend completely across the chart. It does above first panel but then is “short” for each succeeding panel. Any ideas what I’m doing wrong?

Comment from savithri.v
Time: Tuesday, October 13, 2009, 5:27 am

By far, I feel, the most comprehensive resource on dynamic charting is your article in Techtrax ezine. The approach of using column function as reference to the specific column in which data resides (mentioned in the comment above) has been put into use by you there albeit in a different context

The technique of using min & max values to ensure that end date is always > start date in the selected range would save many an embarrassment from nitpickers.

Was psychology ancillary to metallurgy in MIT?

Comment from Josh
Time: Monday, January 11, 2010, 5:47 pm

Hi,
Thanks for the very informative site. Do you know of any problems using this technique using Office ‘08 on Mac?
I have downloaded your sample sets and they all look and behave well. I have tried taking these concepts to a test version of my data, but get errors. if I try to use the =SERIES() function I get a “this is not a valid function” error. if I try to edit the chart and use =Xvalues/Yvalues, I can get the X values to work, but the Y values do not and the X legend shows up as test1!Xvalues.

Im sure it is something very simple that I am missing. any suggestions?
thanks again,
JO