|  | Chart Question | | |
| | | JimS |  |
|  Posted: Mon Sep 08, 2008 10:48 pm Post subject: Chart Question |  |
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? |
| |
| | | Bruce Sinclair | |
| | Posted: Tue Sep 09, 2008 2:19 am Post subject: Re: Chart Question | |
In article <oshbc41l8nkrctj7upmrfb2rdfed20oosb@4ax.com>, JimS <jimx22@msn.com> wrote:
| Quote: | 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 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  |
| |
| | | JimS | |
| | Posted: Tue Sep 09, 2008 7:06 am Post subject: Re: Chart Question | |
| |  | |
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.
On Tue, 9 Sep 2008 01:42:08 -0700 (PDT), Klemen25 <klemen25@gmail.com>
wrote:
|
| |
| | | Klemen25 | |
| | Posted: Tue Sep 09, 2008 8:42 am Post subject: Re: Chart Question | |
| |
| | | Klemen25 | |
| | Posted: Tue Sep 09, 2008 11:58 am Post subject: Re: Chart Question | |
Hm , as I don't know how much you know about naming it is (sorry) bet
to again paste internet link...
Let us know if you suceed...
LINK |
| |
| | | Mike Middleton | |
| | Posted: Tue Sep 09, 2008 3:40 pm Post subject: Re: Chart Question | |
| | | |
JimS -
| Quote: | I can't figure out where to insert>name>define in Excel 2007
|
Formulas (ribbon) | Define Name | Define Name ... or
Formulas (ribbon) | Name Manager | New ...
- Mike Middleton
LINK
Decision Analysis Add-ins for Excel
"JimS" <jimx22@msn.com> wrote in message
news:51fcc41f32s68otkjjqicif0hn0fc0joqp@4ax.com...
| Quote: | 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.
On Tue, 9 Sep 2008 01:42:08 -0700 (PDT), Klemen25 <klemen25@gmail.com
wrote:
Check:
LINK
|
|
| |
| | | JimS | |
| | Posted: Tue Sep 09, 2008 6:10 pm Post subject: Re: Chart Question | |
| | | |
Thanks, didn't see that before.
On Tue, 9 Sep 2008 10:40:37 -0700, "Mike Middleton"
<mike@mikemiddleton.com> wrote:
| Quote: | JimS -
I can't figure out where to insert>name>define in Excel 2007
Formulas (ribbon) | Define Name | Define Name ... or
Formulas (ribbon) | Name Manager | New ...
- Mike Middleton
LINK
Decision Analysis Add-ins for Excel
"JimS" <jimx22@msn.com> wrote in message
news:51fcc41f32s68otkjjqicif0hn0fc0joqp@4ax.com...
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.
On Tue, 9 Sep 2008 01:42:08 -0700 (PDT), Klemen25 <klemen25@gmail.com
wrote:
Check:
LINK
|
|
| |
| | | JimS | |
| | Posted: Wed Sep 10, 2008 10:24 am Post subject: Re: Chart Question | |
| | | |
In case anyone is interested I found out how to accomplish what I
wanted in 2007 here: LINK
On Tue, 9 Sep 2008 10:40:37 -0700, "Mike Middleton"
<mike@mikemiddleton.com> wrote:
| Quote: | JimS -
I can't figure out where to insert>name>define in Excel 2007
Formulas (ribbon) | Define Name | Define Name ... or
Formulas (ribbon) | Name Manager | New ...
- Mike Middleton
LINK
Decision Analysis Add-ins for Excel
"JimS" <jimx22@msn.com> wrote in message
news:51fcc41f32s68otkjjqicif0hn0fc0joqp@4ax.com...
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.
On Tue, 9 Sep 2008 01:42:08 -0700 (PDT), Klemen25 <klemen25@gmail.com
wrote:
Check:
LINK
|
|
| |
|
|
