Archive of Mr Excel Message Board

Check out Bill's new book on Charts and Graphs for Microsoft Office Excel 2007

Back to Charting for Excel archive index
Back to archive home

Expanding charts to update new data

Posted by David Chung on October 17, 2001 8:40 PM
Dear Mr Excel,
This is a variation of an earlier question on this topic. I have four charts per sheet, there are four sheets, each sheet represents a state. I store the data on the fifth sheet. The data is organised into blocks eg: For Product line A: Turnover (NSW,VIC,QLD,SA), Gross Margin (NSW,...); For Product line B: Turnover....etc.
Each month I update the data and all the 16 charts, a tedious, time consuming task as I cannot simply select the data and drop on each chart to update. What is the best way to update all these charts?
Regards
David

Re: Expanding charts to update new data

Posted by doinks on October 17, 2001 11:40 PM
You can try inserting a row between the last and second to the last row in your data sheet.
Just copy the last month's data in the inserted row and put in the new month's data
in the last row.


Using named ranges

Posted by Eric on October 18, 2001 5:10 AM
If you're data started out in A1:A20, try selecting col A, naming the range as "graph". Then start the chart wizard, and instead of typing in the cell references, type in the name of the range, it should graph only those cells with data. To update the graph you could record a macro where you right click on the graph, go Source Data|Data Range, and type "=graph", then Enter.


Recorded macro gives me a 'runtime error 13' on selecting the chart

Posted by Eric on October 18, 2001 5:45 AM
I also tried switching from using the named ranges to just picking the entire column (a:a) but get the same error. Any tips on what I'm doing wrong?


Use dynamic ranges.

Posted by Juan Pablo on October 18, 2001 6:02 AM
If you need help on this, visit this web page.

http://www.beyondtechnology.com/geeks007.shtml

Juan Pablo


My problem

Posted by Eric on October 18, 2001 8:10 AM
Thanks for the URL, I'm afraid I'm still having trouble with the graph updating though.
I make a dynamic range and name it "graph". Then I start the chart wizard, and where it asks for "source data" I type in
=graph
and it gives me just what I want.

The problem is that when I then go back in the spreadsheet and add more values to the bottom of the dynamic range, the chart doesn't update. If I go back in to source data it has the "hardcoded" range, not the named dynamic range, listed. I can retype in the dynamic range to "update" the graph, is there an easier way to keep the graph up to date with what's in the dynamic range?


Re: My problem

Posted by Juan Pablo on October 18, 2001 9:00 AM
Ok, let's assume one thing, just to make sure we're talking the same. In A1 put No, B1 put Value. Now, A2:A7 put numbers 1 to 6, and in B2:B7 put some values, whatever you like.

Now, goto Insert, Names, Define.
First range. Name = "RangeX", Refers To:
=OFFSET(Sheet1!$A$2,0,0,MATCH(9.9999E+307,Sheet1!$A:$A)-1)

Click Add.
Second Range. Name = "RangeY", Refers To:
=OFFSET(Sheet1!$A$2,0,1,MATCH(9.9999E+307,Sheet1!$A:$A)-1)
Click Add.

Now, chart wizzard, type = stacked columns, next, select "Series tab", click Add.
In Values put =Sheet1!RangeY
In Axes (X) put =Sheet1!RangeX
Next, next, and put as object in Sheet1.

You should have your data in A1:B7, and your graph somewhere near it. Now, put something in A8:B8, like {7,5} or whatever you like. The graph SHOULD adjust it self. Now try erasing some data... or copying it from somewhere else...

Hope that helps

Juan Pablo


Thanks Juan!

Posted by Eric on October 18, 2001 12:16 PM
My problem was that I was adding the reference to the dynamic range in the "Data Range" rather than the "series" tab.
Thanks again for following up on that for me- it will help a ton in my work :-)


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.