automatically updating graphs

fidgen

New Member
Joined
Aug 20, 2002
Messages
46
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Aug 20, 2002
Messages
46
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

New Member
Joined
Aug 20, 2002
Messages
46
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!
 

Forum statistics

Threads
1,144,330
Messages
5,723,740
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top