Updating Charts

[Mordred]

Hi all,

Just wondering if someone can point me to a thread or give me advice on how to have a chart update its values after the data ranges have been updated? Specifically, I have a sheet that gets updated monthly, adding the new months data for that update. How can I apply this kind of logic to charts? Here's the recorded macro I have:

Please Login or Register  to view this content.

Thankee big-big!

[Andy Pope]

You can use named ranges in order to have dynamic charts.
http://peltiertech.com/Excel/Charts/Dynamics.html

[Mordred]

Thanks Andy, I've been exploring other threads and especially, looking at your website. I think I'll be able to come up with something today (if IT can fix my computer).

[Mordred]

Hi Andy, I've been trying to implement your scroller.xls into one of our workbooks but am having troubles. Particularly with regards to the "SCROLL_LABELS" and "SCROLL_DATA". In your file, you have this commented out:

Please Login or Register  to view this content.

You state the data set in your example references these named ranges and I get that. What I do not get is where this formula is in the workbook? When I look at the screen shot you provide, the "Values:" has:

Please Login or Register  to view this content.

but I do not see where you set the formula for "SCROLL_DATA". I am confusing my issue here I think but I have been trying to figure this out without much luck. Can you please point me in the right direction on this?

Kindest Regards:

[Andy Pope]

in xl2003 use Insert > Names > Define
in xl2007 use Formula > Name Manager

You will find the names there.

[Mordred]

Hi Andy, thanks so far for your help. I'm not quite understanding the formula now. I now that you have yours set up to scroll down the rows but what about if I need to go across the columns? I've attached a small workbook with one field that I am trying to emulate from your example but there is no data showing up. I don't mean to be a pain in your behind but I have trouble understanding spreadsheet formulas.

[Mordred]

Ok, so I changed the formula a bit so that it looks like this:

Please Login or Register  to view this content.

Now the problem is this: I can get the axis to display months (Jan Feb ...) or Years ( 2006 2007...) but not both. The user would want months and years. Tips?

[Andy Pope]

Because you have the year in merged cells it would be best to create the month/year date in the cells above the current data table.
So enter the dates in row 3.

Then alter your named ranges.

SCROLL_LABEL: =OFFSET(Sheet1!$C$3,0,Sheet1!$B$1,1,Sheet1!$C$1)
SCROLL_DATA: =OFFSET(SCROLL_LABEL,3,0)

[Mordred]

Thank you very much Andy. We have so much historical data that has not been graphed but now it will be, which will make my department a happy department.