automatically updating graphs

[fidgen]

I have a spreadsheet that starts with a data sheet (1000 date rows, 20 odd columns) From this, data is summarised onto a monthly sheet, and various other sheets that have formulae added to them. (so each line represents the sum of 28/30/31 rows on the data sheet, with formulas added)

From these formulae sheets I have line graphs for each of the 20 columns, with a seperate simple bar (0 or 1) as a second series on the same graph. The line shows the monthly movement, and the bar shows seperate instances.

All clear (as mud?)

As it is a date based spreadsheet and is updated monthly, new rows are added to the data sheet each month, and the formula sheets are updated manually as required. No problem.

But updating the graphs to include the extra row of data in the 2 series involves 20x a lot of mouse work.

Is there any way (macro?) to make the graphs automatially update and show the data for each new line that is added to the formula sheets? as the data is in columns, is it possible to make a graph of a column, that adds data points as data is entered in the column?


Sorry this is so long and convoluted, but I didn't want to leave anything important out.


Many thanks
Hugh

 

[thereuare]

Since nobody has replied as of yet, i'll throw in my thoughts which i think should work.

If for example your data is in columnA and extends from Row1 to Row20 (before you add any "new" data").

Have the chart graph the data from A1:A21 (one row below what you need).

I don't know how your code is set up, but i assume it just adds the new data to the end of the column. Rather than just paste to the last row, have the macro insert a row directly below the last line of data before you paste. Using the example above, rather than pasting your date to row 21, the macro should highlight row21, insert a row, and then paste the data to row 21.

This will cause the chart to now chart the range from A1:A22, with your last row of data being A21. When it repeats, inserting a row will cause the chart to read the range A1:A23, while pasting your data into row A22.

Hope this makes sense and you followed it all. Let me know if this helps/works, if it doesn't, let me know how it messes up, and post some of your code.

 

[PaddyD]

If this is right:

Problem
Get excel chart's to update automatically as I add new data to my workbook.

Then:

Solution
Refer to your data in the chart using a dynamic named range. The dynamic named range can be set up so that it will always refer to all your data.

See the conditional chart tutorials on

http://www.tushar-mehta.com

for how this works & post back if you have more questions.

Paddy

 

[fidgen]

Hiya, I'm trying to use the "basic Range" example from the dynamic charts example sheet, as I think this will do the job fine. But I can't get excel to accept XValues or YValues as an "object" (don't know the proper term) ie the OFFSET formula works fine, but when trying to create a chart, The XValues line can't find YValues that is named in the formula, and the chart won't accept the series values='Basic Range'!YValues

how can I get excel to accept X + Y values as a term to build formulae + charts around.

Thanks

Sorry to be so dim, but I've no formal training in excel, I just get to use it a lot - and It now seems to be getting quite technical!


XValues=OFFSET('Basic Range'!YValues,0,-1)

YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

 

[fidgen]

finally worked it out!


I needed to use the "define names" option (insert/names/define) to tell excel that "YValues" wasn't just a random bit of text and that it actually referred to the OFFSET formula.

suddenly things started working!