Chart Auto Update data - VBA?

Question

Hi All,

If I have a chart, and the chart data range is stored in Column A and B as below. And I will add new data daily to column A and B. I want the chart taking all data as I add daily data onto column A and B. How can I do that, do I need VBA?

Date		AU
20-Mar		9,633
21-Mar		9,819
22-Mar		9,750

Thanks in advance.

Answer 1

Create a name for each of the columns, e.g, AUValues refers to

=OFFSET($B$1,1,0,COUNTA($B:$B)-1,1)

HKValues refers to

=OFFSET($C$1,1,0,COUNTA($C:$C)-1,1)

etc.

In the Select Data source dialog, select each of the series in turn, and click Edit under Legend Entries (Series).

Set the source range for the AU series to Book1.xlsx!AUValues, that for the HK series to Book1.xlsx!HKValues etc. (using the actual name of your workbook instead of Book1.xlsx, of course).

Answer 2

You can create dynamic named ranges for this.

Activate the sheet with the source data.

On the Formulas tab of the ribbon, click Name Manager.

Click New...

Enter XValues in the Name box, and enter the following formula in the Refers to box:

=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

Click OK.

Click New...

Enter YValues in the Name box, and enter the following formula in the Refers to box:

=OFFSET($B$1,1,0,COUNTA($B:$B)-1,1)

Click OK.

Click Close to close the Name Manager.

Select the chart.

On the Design tab of the ribbon, click Select Data.

Click Edit under Legend Entries (Series).

Clear the Series values box, and enter the following formula:

=Book1.xlsx!YValues

where Book1.xlsx is the name of the workbook.

Click OK.

Click Edit under Horizontal (Category) Axis Labels.

Clear the Axis label range box, and enter the following formula:

=Book1.xlsx!XValues

where again Book1.xlsx is the name of the workbook.

Click OK.

Finally, click OK to close the Select Data Source dialog.

Your chart will now dynamically adjust itself to new data in columns A and B.

Warning: the definition of the named ranges assumes that there are no other data below the source data in columns A and B.

Answer 3

Date		AU
20-Mar	9,633
21-Mar	9,819
22-Mar	9,750

Great info on dynamic named ranges! 

Over time, adding a data point daily, this chart will become quite long.

Any suggestions to limit the data set to show only the last 20 entries? 

Many thanks,

Heidi

Answer 4

Thank you very much HansV

Answer 5

Hi HansV,

This works very well. Thanks!

What if I have few line in the chart now? How do I set it, it is getting complicated, not so sure how you how you will define the names.

Date	AU	HK	JP	KR
20-Mar	9,633	24,203	43,798	15,240
21-Mar	9,819	26,230	43,842	13,865
22-Mar	9,750	27,522	44,290	11,798