Excel Chart Using a Dynamic Range - Keeps Changing

DJWH

New Member
Joined
May 9, 2008
Messages
43
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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')
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top