Updating graph

[kola5567]

I have several graphs that shows the past 30 sets of data. Every couple days I add new data to the database and I an trying to write a program that will automatically change the source data range to show the latest 30 sets of data. I have tried a lot of things but can't get it right. Anyone have ideas on how they would do this?

[dazman]

If your worksheet was called Summary and the chart was called Chart 9, the example below shows a template for how to define the range:

Sheets("Summary").ChartObjects("Chart 9").Chart.SetSourceData Source:=Range("your_range_here"), PlotBy _
:=xlColumns

You can get the syntax for the range by copying the range data from the chart controls when you update it manually. You can also add variables to the range to expand it dynamically.

[kola5567]

I have that line of code, but am having trouble defining the "your range here" part. I dont know how to define or set up the controls of the range since it will always change. The first cell will change, but the range will always be 30 cell long.

What are the dynamic controls I could try?

[dazman]

In my instance the rows change and the variable NumRows is added outside the quotes with the & as follows:

("=Summary!$A$2,Summary!$A$3:$A$" & NumRows & ",Summary!$H$2:$I$2,Summary!$H$3:$I$" & NumRows & "")

Your range will be different - If you can copy your current chart data and highlight the cell ref you need to change I could show an example

[chris]

You might find it easier to use the "get external data" for this:

1. Build a sheet that will be your input sheet
2. go to a new sheet and "import" your input sheet by following those
steps:
Data/ Import external data/ import data then select the file you are
currently in in the drop-down menu and the sheet that contains your
input data.
3.You should now get on this sheet exactly the same data as on the
input sheet, but it will be referred to with a rangename (should be
name of file).
4.Base your graphs on THIS sheet, not the input sheet.
5. Add new data on the input sheet.
6 Goto the sheet that contains the imported data and rightclick
anywhere on the data. You should get a red "!". If you click this, your
import will be refreshed - and your graphs will refresh automatically.

Hope that helps

[chris]

You might find it easier to use the "get external data" for this:

1. Build a sheet that will be your input sheet
2. go to a new sheet and "import" your input sheet by following those
steps:
Data/ Import external data/ import data then select the file you are
currently in in the drop-down menu and the sheet that contains your
input data.
3.You should now get on this sheet exactly the same data as on the
input sheet, but it will be referred to with a rangename (should be
name of file).
4.Base your graphs on THIS sheet, not the input sheet.
5. Add new data on the input sheet.
6 Goto the sheet that contains the imported data and rightclick
anywhere on the data. You should get a red "!". If you click this, your
import will be refreshed - and your graphs will refresh automatically.

Hope that helps

[chris]

You might find it easier to use the "get external data" for this:

1. Build a sheet that will be your input sheet
2. go to a new sheet and "import" your input sheet by following those
steps:
Data/ Import external data/ import data then select the file you are
currently in in the drop-down menu and the sheet that contains your
input data.
3.You should now get on this sheet exactly the same data as on the
input sheet, but it will be referred to with a rangename (should be
name of file).
4.Base your graphs on THIS sheet, not the input sheet.
5. Add new data on the input sheet.
6 Goto the sheet that contains the imported data and rightclick
anywhere on the data. You should get a red "!". If you click this, your
import will be refreshed - and your graphs will refresh automatically.

Hope that helps

[Tushar Mehta]

No need for VBA. See
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> I have several graphs that shows the past 30 sets of data. Every couple
> days I add new data to the database and I an trying to write a program
> that will automatically change the source data range to show the latest
> 30 sets of data. I have tried a lot of things but can't get it right.
> Anyone have ideas on how they would do this?
>
>
> --
> kola5567
> ------------------------------------------------------------------------
> kola5567's Profile: http://www.excelforum.com/member.php...o&userid=24614
> View this thread: http://www.excelforum.com/showthread...hreadid=399071
>
>