When using Excel to display data from a datalogger or device in real- time, you may find it useful to also automatically update a chart in Excel.
Suppose you wanted to plot data in column A against the data in column B. You could simply insert a chart with the range =$A:$B
As the data arrives from Windmill DDE Panel, the chart will automatically update. However, if the rows of data exceed the original size of the worksheet, the chart will stop. You can get round this by using Excel's CountA and Offset functions.
CountA gives you the number of non- blank cells in a column. With Windmill data this equates to the number of rows of data (and headings). Offset returns a cell reference according to your settings.
Using CountA as the height of the column gives a reference to the bottom row. This reference will automatically update when new data arrives. For example, this is the syntax of Offset: OFFSET(reference, rows, cols, height, width) which becomes: OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)) (assuming your data is in a worksheet called "Sheet1").
To implement this, using the previous example of plotting column A against column B. |