Dynamic Charts - help setting up with named ranges

**Alex Aherne** · 06-08-2012, 09:35 AM

Hi,

I am new to posting on here as I usually find the answers I need by searching. However I'm coming up blank this time so I'm hoping someone can help me out.

I have data which I'm trying to chart. The numbers of rows in my data changes from month to month but the columns remain fixed. I have attached an example showing my data and to the right of it the chart. Currently the chart is set up with the data range specified as C2:O21. I want my chart to dynamically update when new rows of information are added.

I have searched and found lots of examples using the OFFSET function and I can create named ranges using this function (you will see some in the Name Manager). However I am having trouble applying these named ranges to my chart. The horizontal axis must be the column headings (year/period) and these never change, however when it comes to plotting the vertical axis I'm having trouble working out how to specify the category shown in column C and the values for each year/period.

A lot of the examples I have viewed have the data set up in a different way to mine and I can't work out how (if it's possible) to do what I need.

Any help much appreciated and I hope I've posted enough information.

Thanks,
Alex

**DBY** · 06-08-2012, 11:57 AM

Hello
It's difficult to add a series to a chart dynamically the way your data is set up. If you're happy to create an Excel Table then you can produce a chart which updates automatically but it requires the date to be pivoted round. Take a look at the attached file, on the Table tab the chart updates as you add new columns. It also allows you to filter the data and chart.

Hope this helps.
DBY

**Andy Pope** · 06-09-2012, 05:24 AM

As DBY says the easiest way to have dynamic series, rather than extending data points, is to use a Table.

But there is no need to alter you existing table, other than Header row in single row rather than over 2 rows.
You can simple switch the data so rows are used as series.

**DBY** · 06-09-2012, 07:49 AM

Thanks Andy
I just couldn't get it to work without pivoting the data around. Happy to learn from the Master.

Regards
DBY

**Alex Aherne** · 06-10-2012, 04:58 AM

Hi,

Thank you for both of your replies.

The table appears to work well however I have a couple of problems with it. I am unsure which version of Excel this report/chart will be run on, it could potentially be used in 2003 and I didn't think the table would work in this version?

Also my header row with the year/period numbers are formulas. Using them in a table removes the formulas doesn't it?

Lastly, the number of rows changes each time the report is run and has the potential to become shorter (less rows) than previously. Does this cause any issues with tables and charting from a table?

Thank you for all your help.

Alex

**Andy Pope** · 06-10-2012, 07:17 AM

In xl2003 tables are called lists.
Correct, you can not use formula as column headers.
If the table is resized the chart will reflect this. If you overwrite the top part of the table only then previous information will still be included.

You could use a pivot table/chart. whilst this would be dynamic in terms of series, once the table was refreshed, the labelling of categories would required a manual change.

Or you could use VBA to auto the process.