Automatic Chart Update?

  • Thread starter Thread starter Mike Fox
  • Start date Start date
M

Mike Fox

Is there a way to automatically update a chart when a data point is
added without redrawing the chart? Went to Tool/Options/ Calculate
tab and the automatic update feature is checked.

I'm running Win XP Pro with Office Pro.

Thanks

Mike
 
One way is to define a named range that is self adjusting and use that as
your series.
insert>name>define>name it someting>in the refers to box type
=offset($a$1,0,0,counta($A:$A),6) Look at HELP index for OFFSET and adjust
in your series, type in (modify to suit)
=myworkbook.xls!myseries
 
Advancing this somewhat... is there a way to deal with multiple series in the
same data column?

The data is sorted so that the each series is in an unknown range in the
column i.e. the range changes as data is added. Thanks in advance for your
help..... Tony G
 
Tony -

You mean update several series? How do you know how to select the data manually? Can
you duplicate this with detection with a set of defined names? Alternatively, if
there is a key of some sort in an adjacent column, you might be able to use a pivot
table to separate the data into columns.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Yes. There is a separate data column (singular) that distinguishes each
series, generally a text identifier. The data set is sorted on this to
separate each series. Having to deal with many data sets, the number of
records in each series changes with each data set. Consequently, an auto
update would be a time-saver. "Counta" as Don suggest works well with one
series. Will "CountIf" work in the OFFSET dialogue? I have tried it without
success so far, likely because I don't fully understand it.

Tony
 
Tony -

I defined a range "WholeRange" which consisted of the range with sorted text
identifiers. Based on this I defined another range, "PartRange" with Refers To as
follows:

=OFFSET(WholeRange,MATCH("a",WholeRange,0)-1,1,COUNTIF(WholeRange,"a"),1)

where "a" was one of the text identifiers. PartRange refers to the range of cells in
the column to the right of the block of cells containing "a". I made a chart, and in
the Source Data step of the wizard, on the Series tab, I used this for the Y Values
of the series:

=Sheet1!PartRange

The chart displayed the appropriate values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top