Check out our Excel Charting Resources | ||||

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

Back to archive home

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

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

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.

Just copy the last month's data in the inserted row and put in the new month's data

in the last row.

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.

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?

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

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

Juan Pablo

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.

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?

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

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

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.

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.