Automatically Update Charts (Dynamic Ranges?)

Here is a sample workbook:

Book1.xlsx

I have a macro that inserts new data into the analysis sheet daily. So the line graphs take into consideration the date column A and the balance column I (for the first section). I want to update my macro or do something so that when the current macro inserts the new daily data, the graphs beneath the data update automatically incorporating the new data just pasted in from the macro.

Any ideas?

Anyone have any suggestions?

Try using 2 Dynamic Named Ranges for your Chart Data..

For the Balance data:

=OFFSET(Analysis!$I$2,0,0,COUNT(Analysis!$I:$I)-1,1)

For the Date data:

=OFFSET(Analysis!$A$2,0,0,COUNTA(Analysis!$A:$A),1)

And refer to these Named Ranges in your Chart instead of static values..

I have attached your Workbook with the first chart on the Analysis sheet done..
Look in Formula Tab > Name Manager to see the 2 Named Ranges..

Note:Your data entry area only allows for 4 lines of data.. I am presuming you are going to make it so that expands as you enter more data?

Originally Posted by apo

Try using 2 Dynamic Named Ranges for your Chart Data..

For the Balance data:

=OFFSET(Analysis!$I$2,0,0,COUNT(Analysis!$I:$I)-1,1)

For the Date data:

=OFFSET(Analysis!$A$2,0,0,COUNTA(Analysis!$A:$A),1)

And refer to these Named Ranges in your Chart instead of static values..

I have attached your Workbook with the first chart on the Analysis sheet done..
Look in Formula Tab > Name Manager to see the 2 Named Ranges..

Note:Your data entry area only allows for 4 lines of data.. I am presuming you are going to make it so that expands as you enter more data?

I see it works perfectly on your workbook!

Couple of questions on how you managed to do it. I've named the ranges like you said. I've also edited the chart by the following:

1.) Right click the chart and click "select data"
2.) Hit edit on the legend entries (series) and entered the series name and series value like you did
3.) Hit edit on the horizontal axis labels and entered the axis label range like you did

Is there anything I need to do? Because mine is not updating like yours

mm.. that should do it.. easiest if you just attach your workbook so i can see if something is wrong there..

By memory.. I think i deleted your horizontal and legend series then added new ones using the Named Ranges..

But i just edited your second chart and it worked fine...

Originally Posted by apo

mm.. that should do it.. easiest if you just attach your workbook so i can see if something is wrong there..

By memory.. I think i deleted your horizontal and legend series then added new ones using the Named Ranges..

But i just edited your second chart and it worked fine...

Must have been doing something wrong. Got it now though. Thanks for all your help! I really appreciate it!

No worries.. glad to help..