Dynamic charting

**eonizuka** · 06-17-2009, 03:11 PM

I've gone through some tutorials on dynamic charting but I still can't get the syntax for the named ranges right. I was hoping someone would be willing to help me set up the dynamic chart for this test file. The ranges I would like to update dynamically are highlighted in yellow. It is going to be the last 4 columns. The chart is on the "Team Trending" tab. The labels are in cells G26:G29.

**Greg M** · 06-17-2009, 04:57 PM

Hi there,

One of the problems with creating named formulas is that any non-absolute cell addresses contained in them will be relative to whatever cell was active WHEN THE FORMULA WAS CREATED. Sometimes this is exactly what you want, sometimes it isn't!

Whenever possible I define the start of a dynamic range as a Name (e.g. ptrStartCell), and use this as the basis of the formula which defines the dynamic range.

If you want to use Cell X26 (the first of the yellow cells) as the start of your dynamic range, then define this cell as "ptrStartCell" and use the following formula as the "Refers to" property of the named formula which you create:

Please Login or Register  to view this content.

10 is the value of the MAXIMUM number of rows you are likely to need in the range - you can have as many as you like, but it's not really good practice to use the entire column;

4 is the value of the ACTUAL number of columns which you wish to include in your dynamic range.

I hope this helps - please let me know how you get on.

Regards,

Greg M

**eonizuka** · 06-17-2009, 05:31 PM

Hi Greg,
Thank you for your help with this. I have attempted to set the named ranges up using your method, however I'm obviously doing something wrong. If you don't mind would you please fix the named ranges in the attached file and adjust the references in the chart as well. I gave it a go for a good 30 minutes but I'm still having trouble...I don't quite understand the syntax that you used. I attempted to add an additional column of data but the ranges are not updating correctly. Additionally, the data that is being added is being added on as additional columns and not rows. Thanks for your help...

**Greg M** · 06-17-2009, 08:55 PM

Hi again,

I've modified the formulas so that they include additional columns rather than rows. In the formula:

Please Login or Register  to view this content.

4 is the ACTUAL number of rows included in the range, and
10 is the MAXIMUM number of columns you are likely to need.

Having said that, I think there's still a problem because the newly-added columns don't represent just additional DATA for your chart, they represent additional SERIES, and I don't think the chart will add extra series just because the range has expanded.

One possible way around this is to reorganise the chart so that four series are created at the start - i.e. Greeting, Solution, Discovery and Closing - each contained in its own dynamic range, and to allow the (already existing) series to expand as data are added and the dynamic ranges expand to cater for them.

I know it's not great when someone suggests that your carefully thought out arrangement should be changed, but it's all I can think of at the moment.

I hope the above is of some assistance to you.

Regards,

Greg M

**eonizuka** · 06-18-2009, 11:58 AM

Thanks Greg. I actually inherited this report from someone else so if necessary I can rearrange it to accommodate your recommended method.

**eonizuka** · 06-18-2009, 12:46 PM

I added the named ranges to the chart and it is updating the new ranges/series, however I am not sure how to have it give me only the last 4 values from the end of the column. Maybe I'm not quite clear on the distinction between series and ranges that you made earlier. If anybody has any suggestions/observations please feel free to do so....What we are attempting to do is to have the last 4 columns updated dynamically on the chart.

Update: Actually the chart is not being updated with the new ranges...

**Chance2** · 06-18-2009, 05:48 PM

I used a few more named ranges, but I got the desired results (I think).

**eonizuka** · 06-18-2009, 06:37 PM

Thank you both for your help with this. Would you mind, Chance2, briefly going over how you set this up?

**eonizuka** · 06-18-2009, 06:46 PM

Originally Posted by Greg M

One possible way around this is to reorganise the chart so that four series are created at the start - i.e. Greeting, Solution, Discovery and Closing - each contained in its own dynamic range, and to allow the (already existing) series to expand as data are added and the dynamic ranges expand to cater for them.

Greg M

Is the distinction between series and ranges that series are plotted on the X axis while ranges are plotted on the Y axis? From most tutorials that I have seen the ranges are plotted along the Y axis(down rows) rather than across columns(X axis). I thought it would involve simply switching around some syntax in order to dynamically update charts with data laid out the way it is on the spreadsheet in this thread...

Update: I understand the distinction now. Each row of data in my spreadsheet represents a series. The 4X4 grouping of data represents 4 different series. For some reason I was fixated on the date range only.. Thanks both of you for help with this.