Automating charting? Way to?

[StargateFan]

Once we know all the elements we need to put together a chart, is
there way to automate the end result somehow? I have a complex set of
charts that I'll need to create monthly, then quarterly and then
yearly. I'm going to have a hard time remembering all the elements to
include. While I'm going to make a "tutorial" with a whole bunch of
screenshots, was hoping Excel had a way for us to just keep up a
spreadsheet, select and area, and then, hopefully as easily as with
the press of a button, create the charts needed?

Is such a thing possible, though?

 

[Jon Peltier]

You can write a VBA program to do this, though it's tricky to create,
modify, and maintain such a program.

You could save the worksheet, then whenever you need to recreate the chart,
copy the sheet into another workbook, then copy the new data and paste it
over the old data in the chart's worksheet. If you save the worksheet as a
template, it will be reasonably easy to insert the sheet into an existing
workbook: right-click a sheet tab, choose Insert, and select the sheet
template from the Insert dialog.

- Jon

 

[StargateFanNotAtHome]

You can write a VBA program to do this, though it's tricky to create,
modify, and maintain such a program.

You could save the worksheet, then whenever you need to recreate the chart,
copy the sheet into another workbook, then copy the new data and paste it
over the old data in the chart's worksheet. If you save the worksheet as a
template, it will be reasonably easy to insert the sheet into an existing
workbook: right-click a sheet tab, choose Insert, and select the sheet
template from the Insert dialog.

Thanks. I was afraid of that. No go. There's not going to be any
other way, then, than to do it manually and to just make tons of
screenshots. I can do a lot of basic things now in VB but nothing
that complicated. Thanks so much for the info, though. Appreciate
it.

 

[Jon Peltier]

Try the template approach. It can prevent a lot of the manual tedium.

- Jon

 

[StargateFanNotAtHome]

Try the template approach. It can prevent a lot of the manual tedium.

Okay, I think I missed something. When the values are the same, that
might be do-able, but I always come back to the same problem, how do
you change the source data in a chart? I guess that's what I'm
missing and unlike other Excel functions, my charting experience and
skills are practically nonexistent.

Thanks! D

 

[Jon Peltier]

You mean when the values occupy the same sized range? Yes, that's the easy
case.

If you have simple changes, like from column $C to column $D, or row $8 to
row $12, you could use the Change Series Formula utility from my web site:

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon