Chart Question

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

I want to make a chart that tracks the progress for a column of data.
I can make a chart now for the data that I have, but how do I make the
chart grab new data that is inserted every day?

I know I can drag down x number of rows on the column, but is there a
way to just have the data insert itself one row at a time as data is
entered?
 
I want to make a chart that tracks the progress for a column of data.
I can make a chart now for the data that I have, but how do I make the
chart grab new data that is inserted every day?

I know I can drag down x number of rows on the column, but is there a
way to just have the data insert itself one row at a time as data is
entered?
Click to expand...

I think the answer here is to choose a larger range than you have for actual
data already, then make sure you insert rows into your data (ie before the
"last" row). You may want to name this range and use the name in your chart.

Be warned ... it's been a long time since I've done this so this may not
apply to the newer versions of X.L :)
 
Thanks. I went to that site and read everything, but I can't figure
out where to insert>name>define in Excel 2007. Here are the
instructions from the web site.

1. Enter the data and create the chart shown in the figure.
2. Select Insert, Name, Define to bring up the Define Name dialog
box.
3. In the 'Names in workbook' field, enter Date. In the 'Refers to'
field, enter this formula:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)
4. Click Add to create the name. Notice that the OFFSET function
refers to the first data point (cell A2) and uses the COUNTA function
to get the number of data points in the column. Because column A has a
heading in row 1, the formula subtracts 1 from the number.
5. Now type Sales in 'Names in workbook', and in 'Refers to' enter
this formula:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
6. Click Add, and then OK to close the dialog box.
7. Activate the chart and select the data series. In this example,
the (unmodified) formula in the formula bar will read:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10, Sheet1!$B$2:$B$10,1)
8. Replace the range references in the SERIES formula with the
names you defined in steps 4 and 5. The SERIES formula should read:
=SERIES(,Sheet1!Date,Sheet1!Sales,1)

After performing these steps, you'll find that the chart updates
automatically when you add new data to the worksheet.

To use this technique for your own data, make sure that the first
argument for the OFFSET function refers to the first data point, and
that the argument for COUNTA refers to the entire column of data.
Also, if the columns used for the data contain any other entries,
COUNTA will return an incorrect value.
 
Back
Top