Chart Source Data Ranges Changing when Data Sheet updated from text file source.

[Tekn0]

I have a simple Excel wb with 2 sheets. Sheet 1 is simple Line Charts.
Sheet 2 is the data for these charts. The data comes from a text file via
the 'Import External Data' tool.
The text file is filled throughout the day with data every 5 minutes
starting at 00:00. Because the chart will contain 288 data points I've set
my series values to"=<data sheet name>!$C$2:$C$289"

I have 2 questions.
1) Whenever I refresh the Data Sheet from the external text file the series
values in my charts increment by the number of new data points. This is no
good as it causes my charts to appear differently throughout the day. What
could be the cause?

2) Is there a way to chart just cells which have data in them? If I set
the series values in a chart to "=<data sheet name>!$C:$C" it creates a
chart with 65535 data points all but a few blank and the chart is no usable.

Thanks,
--S

 

[Bernard V Liengme]

See example of dynamic chart at www.stfx.ca/people/bliengme/ExcelTips
Best wishes
Bernard

 

[Jon Peltier]

1) Won't the charts appear differently as data is appended to the
series? Or is there a difference I don't understand?

2) You can set up dynamic ranges that contain only the non blank part of
the data range, and chart these. Here's an overview, with examples and
more links:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Tekn0]

Sure the chart does change, but what is happening is that because there will
only ever be 288 data points, the refresh of the data adds additional length
to the series values. The result at the end of the day is a chart of the
days data, that only spans the front half of the chart.

I think your dynamic examples are going to do exactly what I need. Thanks
for the link.

--S