Dynamic charts in 2007

[ker_01]

I've used dynamic charts in 2003 for several years. I now have an internal
customer who has moved to 2007 and would like some dynamic charts. I obtained
access to a 2007 machine, loaded his current workbook, and noted that the
interface is very different.

I am accustomed to right-clicking a chart, selecting 'source data', then
going into the series tab to put the named range in.

In this workbook, I right click and the closest option I have is 'select
data' which brings up a 'select data source' userform that is set to a full
range (multiple series) of data.

Since this is my first exploration of Excel2007, I don't know if this is an
issue with my not knowing where to look, the setup of this particular
file/graphs, or something about 2007 overall.

I did google and revisit all the sites I knew about that had info on dynamic
charting, but they all seem to point toward the individual series like what
I'm used to in 2003.

Can anyone give me a nudge in the right direction?

Thanks!
Keith

 

[ker_01]

Ok, more searching and more playing, and I'm a lot closer than I was. Now
I've hit something that appears to be a bug(?)

From my user's data table, I created 7 named ranges. The first is the real
dynamic named range [=OFFSET(Graphs!$B$14,0,0,1,COUNT(Graphs!$B$14:$M$14))]
and all of the rest are offsets from that range[ e.g., =OFFSET(NCR, 1,0)].
All have the scope 'workbook'.

So when I enter new data into the table, all of the series show their new
entries *except* my main named range, NCR. That one just stays blank on the
graph. The named range is autoexpanding to include the new months, they just
aren't showing on the graph. Any entries in the other named ranges do show as
expected.

Any ideas why my primary range isn't updating?

Thanks!
Keith

 

[Jon Peltier]

Excel 2007 has "clarified" things by combining the series and data range
tabs. To edit the series information, you select the series in the list
below the data range and click the Edit button.

Of course, I've always found it easier to edit the series formula, replacing
the cell address with the name (while keeping the reference to the worksheet
name). This works the same in all modern versions of Excel. The only
difference is that 2007 chokes on names that begin with the keyword "chart",
so instead of using names like "chartdate" and "chartvalue" use "chtdate"
and "chtvalue".

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

Ok, more searching and more playing, and I'm a lot closer than I was. Now
I've hit something that appears to be a bug(?)

From my user's data table, I created 7 named ranges. The first is the real
dynamic named range
[=OFFSET(Graphs!$B$14,0,0,1,COUNT(Graphs!$B$14:$M$14))]
and all of the rest are offsets from that range[ e.g., =OFFSET(NCR, 1,0)].
All have the scope 'workbook'.

So when I enter new data into the table, all of the series show their new
entries *except* my main named range, NCR. That one just stays blank on
the
graph. The named range is autoexpanding to include the new months, they
just
aren't showing on the graph. Any entries in the other named ranges do show
as
expected.

Any ideas why my primary range isn't updating?

Thanks!
Keith

I've used dynamic charts in 2003 for several years. I now have an
internal
customer who has moved to 2007 and would like some dynamic charts. I
obtained
access to a 2007 machine, loaded his current workbook, and noted that the
interface is very different.

I am accustomed to right-clicking a chart, selecting 'source data', then
going into the series tab to put the named range in.

In this workbook, I right click and the closest option I have is 'select
data' which brings up a 'select data source' userform that is set to a
full
range (multiple series) of data.

Since this is my first exploration of Excel2007, I don't know if this is
an
issue with my not knowing where to look, the setup of this particular
file/graphs, or something about 2007 overall.

I did google and revisit all the sites I knew about that had info on
dynamic
charting, but they all seem to point toward the individual series like
what
I'm used to in 2003.

Can anyone give me a nudge in the right direction?

Thanks!
Keith