Volatile source data range

  • Thread starter Thread starter Nick H
  • Start date Start date
N

Nick H

I'm trying to construct a template that does some data analysis and plots the
results, and I am only one step away from getting what I need. The issue is
that the length of my source data table varies for each data set I analyze.
I have the charts made up, but as it is now I must manually change the source
data range each time I use the template. Ideally, I would like to enter the
source data range with a function like INDIRECT (because I can generate the
range as text in the spreadsheet). The Source Data window won't accept this,
however. Is there some other way to do this? Thanks.
 
Just thought of this, but if possible I'd like the chart titles, etc., to be
read from the spreadsheet, too, if there is a way. Thanks!
 
Hi,

You can do this with dynamic range names but if the data is laid out
vertically and you are using Excel 2003 or 2007 then I recommend a List or
Table. You data must have one row of titles directly above the data:

Select the data range and choose Data, List, Create List, click OK. The
chart will use this range and when data is added below it the chart will
automatically adjust. In 2007, choose the command Home, Format as Table and
pick any style and click OK. If you don't want the fancy formatting you can
later choose the top left style, from the Table Style gallery.
 
Thanks, Shane. I tried this, and something strange happened. FYI, I'm using
Excel 2003 and making a log-log scatter plot. When I created a list with a
header for my X data, and then substituted the header for the range in the
chart, it changed the data from the cell content to the row number. For
example, the X value in cell A18 is 365. When I made the change you
suggested, it plotted the corresponding Y value at 18 on the X axis, rather
than 365. Any ideas?
 
Back
Top