How to graph adjacent data in column?

  • Thread starter Thread starter bmacwilliams
  • Start date Start date
B

bmacwilliams

I have data of variable length (time dependent) that I wish to graph
which comes from some third party software. The data always starts in
the same row and is always in the same column (C29). The problem is
that after the data rows end, there are several blank rows, and then
other data is written below in the same columns of interest. I have
created an Excel template which graphs this data, with fixed ranges of
rows (C29:A55). For shorter data sets however, the extraneous data
becomes included in this range, and this causes all kinds of trouble
with the graph. Is there a way to set the data range so that the last
non-blank cell is determined, something like (C29:<End>), so that only
the adjacent data is plotted? I know you can do this with keyboard
shortcuts and I know I can make a macro do this, but is there a way to
include this within a data range specified inside a chart?
 
Jon Peltier has instructions for creating a dynamic chart.

http://www.geocities.com/jonpeltier/Excel/Charts/DynamicCharts.html

Substitute the following formula as the definition of myXvalues --

=OFFSET(Sheet1!$C$29,0,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$C$29:$C$55),0,1),0)-1,1)

and it should use the cells from C29 to the cell above the first blank
cell in the range, assuming there will be at least one blank cell in the
range.
 
Back
Top