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

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.

Posted by Eric on October 18, 2001 5:10 AM

Using named ranges

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.

Posted by Eric on October 18, 2001 5:45 AM

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

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?

Posted by Juan Pablo on October 18, 2001 6:02 AM

Use dynamic ranges.

If you need help on this, visit this web page.

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

Juan Pablo

Posted by Eric on October 18, 2001 8:10 AM

My problem

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?

Posted by Juan Pablo on October 18, 2001 9:00 AM

Re: My problem

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

Posted by Eric on October 18, 2001 12:16 PM

Thanks Juan!

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 :-)