If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Chart Source Data Ranges Changing when Data Sheet updated from text file source.
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 |
#2
|
|||
|
|||
Chart Source Data Ranges Changing when Data Sheet updated from text file source.
See example of dynamic chart at www.stfx.ca/people/bliengme/ExcelTips
Best wishes Bernard "Tekn0" wrote in message ... 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 |
#3
|
|||
|
|||
Chart Source Data Ranges Changing when Data Sheet updated fromtext file source.
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Tekn0 wrote: 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 |
#4
|
|||
|
|||
Chart Source Data Ranges Changing when Data Sheet updated from text file source.
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 "Jon Peltier" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ |
Thread Tools | |
Display Modes | |
|
|