Auto Update Chart - excel 97 to 2003

**Nik_AU** · 12-26-2007, 05:22 AM

Hey,

I have been doing more work on my report and could not find a direct answer anywhere. I found an answer for excel 97 but excel 2003 I have to make some changes to get this to work. So I would like to post it here in case I ever need to search for it again.

Enter the data and create the chart. (This chart has 2 colums - Column A = Date - Column B = Sales) - see http://j-walk.com/ss/excel/usertips/tip053.htm

Select Insert, Name, Define to bring up the Define Name dialog box.

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))

or =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1) -> add -1 if the chart goes down 1 row to far ( mine did in one chart for some reason??)

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.

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)

Click Add, and then OK to close the dialog box.

Now right click on the chart and choose "source Data" - Click the series tab -and in 'values' enter this formula:. 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)

Replace the range references in the SERIES formula with the names you defined in steps 4 and 5. The SERIES formula should read: (below for excel 2003)

='Sheet1!'Date for the Date axis
='Sheet1!'Sales for the sales axis

(The above 2 lines were the major difference with excel 97 & 2003)

For excel 97 use the below

=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.

**mdixon** · 01-17-2008, 06:12 PM

My workbook has it's data spanning columns instead of rows. One row may span from B to AA. This data is updated every month, and I'd like for my charts to dynamically update. My data begins in row 3, column B. I created a named range that refers to the following formula:

=OFFSET(Data!$B$3,0,0,COUNTA(Data!$B:$B),COUNTA(Data!$3:$3))

I thought this would work, and automatically expand my range when I added a new column. But when I tried to change my chart's source data to my new named range, (Dynamic1), I received error messages.

What am I doing wrong?