Using dynamic range names in Excel Charts

[austen447]

I want an Excel graph to automatically amend the source range, as the number of data plots in the source range changes. I have a dynamic range name that adjusts to the number of columns in the range, called "graphone" and setup under "insert-name-define" as
=OFFSET(Results!$D$11,0,0,Results!$AA$7,2)
where $aa$7 stores the number of lives column entries in the range.
I can specify this range name when setting up a Chart in Excel 2002, using the "source data" "data range" field.
However, Excel then converts this to a fixed cell range, i.e. if the range definition changes dynamically at a later time, the Excel Chart source data range does not get updated.
Is there a simple way, or another step, to ensure that the graph source data updates dynamically also?
regards
austen447

 

[jdhilljr]

Check out this thread.

javascriptpenindex(450,350,'

http://www.tipmaster.com/includes/refinfo.cfm?w=450&h=350')

 

[PeterMoran]

Hi austen447,

I think the following may help assuming that you want to chart all the figures in a range, not just what you want by entering that number in AA7.

Try replacing your Results!$AA7 with the COUNTA function which saves you having to enter in AA7 the number of entries.

=OFFSET(Results!$D$11,0,0,COUNTA($D$11:$D$999)-1,2) assuming D11 is the column header.

If you continue to have problems I can send you an article - "A Dynamic Chart that Plots the Most Recent X Items in a Range " but I will need an email address.

Good Luck!
Peter Moran
Two heads are always better than one!

 

[austen447]

Peter, thanks for the post. I'd be interested in that article; my email is jetset07 at myway.com
my "aa7" cell already contains a COUNTA function set out as you suggest. Thats not the problem - the range does dynamically adjust depending how many entries are there.
The problem is that the Excel Chart does not then reflect the newly adjusted range, since, when you input a range name into the Chart, Excel converts it to a static range reference.
I have done a work-around, in which some VBA code of mine hides the columns with no data - using the fact that an Excel Chart will display only unhidden rows or columns, but this is more cumbersome.
regards
Phil

 

[jdhilljr]

http://procollectortips.com/graph.xls

This is how I set up some of my graphs to allow to look at current and historical data.

Jim

 

[austen447]

thanks for the post jdhilljr, not quite what I'm wanting to do, but I like the approach which is very user friendly.