Excel Chart Using a Dynamic Range - Keeps Changing

[DJWH]

Hi,

I wonder if anyone can help.

I have a chart that uses a dynamic named range to update automatically. I also have a drop down list which allows me to view the chart for a selected product. When I select an item from the drop down list, the data in my named range changes. However when I then go into my chart, and right click, source data, the chart is not looking at the named range anymore... It's looking at the direct reference to these cells.

Not sure if I am explaining it very well. Let me know if I need to provide more details.

Any help appreciated.

Thanks

 

[Derek Brown]

You did not say which version of Excel is being used but I wonder if it is Excel 2007 or 2010.
Have a look at Bill's posts at http://www.mrexcel.com/forum/showthread.php?t=541307 - this may be the problem that you have.
Ignore the second sentence in my post #7 (I don't know why I put that there!).

 

[DJWH]

You did not say which version of Excel is being used but I wonder if it is Excel 2007 or 2010.
Have a look at Bill's posts at http://www.mrexcel.com/forum/showthread.php?t=541307 - this may be the problem that you have.
Ignore the second sentence in my post #7 (I don't know why I put that there!).

Thanks however it still doesn't work. I have also added the name of the Worksheet to the chart reference but as soon as I click out of the chart and then view the Source Data, it's back to looking at the actual cells reference. Could I add a VBA macro to extend the chart reference by one column each time I add data?

 

[Derek Brown]

If anyone is able to help you with the VBA, it would be useful for them to know what version of Excel is being used.
Bill's post #3 in that thread indicates that if your data is an Excel Table, then the chart is likely to extend with the data.

 

[DJWH]

If anyone is able to help you with the VBA, it would be useful for them to know what version of Excel is being used.
Bill's post #3 in that thread indicates that if your data is an Excel Table, then the chart is likely to extend with the data.

I'm using Excel 2003.

I have updated my table with additional data however the chart does not update automatically.

 

[Derek Brown]

Ah, Excel 2003 - that makes a difference - my previous comments were regarding 2007/2010.
How are you referring to the range in the chart?
If the range name is "MyRange" and the workbook name is "My Book.xls", then in the chart you would need to use something like:
='My Book.xls'!MyRange
That is assuming that MyRange has workbook scope.
You will find a great source of chart information at:
http://peltiertech.com/Excel/Charts/ChartIndex.html
(See the section on 'Dynamic Charts')

 

[DJWH]

Ah, Excel 2003 - that makes a difference - my previous comments were regarding 2007/2010.
How are you referring to the range in the chart?
If the range name is "MyRange" and the workbook name is "My Book.xls", then in the chart you would need to use something like:
='My Book.xls'!MyRange
That is assuming that MyRange has workbook scope.
You will find a great source of chart information at:
http://peltiertech.com/Excel/Charts/ChartIndex.html
(See the section on 'Dynamic Charts')

I've tried the ='My Book.xls'!MyRange but it doesn't work still. Thanks for your reply though. How do I knoe if my range has workbook scope? I created this using the usual Insert - Name - Define option.

I'll have a look at the link you sent too.

 

[DJWH]

I've tried the ='My Book.xls'!MyRange but it doesn't work still. Thanks for your reply though. How do I knoe if my range has workbook scope? I created this using the usual Insert - Name - Define option.

I'll have a look at the link you sent too.

When I click on the series I get the following in the formula bar:

=SERIES('Sales Data for Plot'!$A$4,'Sales Data for Plot'!$B$3:$Q$3,'Sales Data for Plot'!$B$4:$Q$4,1)

where the sheet named 'Sales Data for Plot' contains the data.

Where would I replace my range name which is called 'SalesDataforPlot' ?

I tried SERIES(Range Name) but that didn't work.

Thanks

 

[Derek Brown]

I am not sure whether this can be changed in the formula bar to use range names, but if you have a look at Jon's examples at:
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
you will see that one of them is:
=SERIES(Sheet1!$B$1,Sheet1!ChartLabels,Sheet1!ChartValues,1)
(Although, from my earlier example, I use the workbook name and range name).
I am using Excel 2010 where the scope can be seen in Name Manager (Ctrl + F3)

 

[DJWH]

I am not sure whether this can be changed in the formula bar to use range names, but if you have a look at Jon's examples at:
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
you will see that one of them is:
=SERIES(Sheet1!$B$1,Sheet1!ChartLabels,Sheet1!ChartValues,1)
(Although, from my earlier example, I use the workbook name and range name).
I am using Excel 2010 where the scope can be seen in Name Manager (Ctrl + F3)

Thanks Derek. I'll go through these examples