Chart and Axis Titles relative reference

[Steve]

I have a very large number of charts I need to reproduce in a
spreadsheet to link to a series of reports.

The charts are similar but the titles and axis titles will vary with
each chart. Some of the charts also have text boxes overlain to show
information that the chart capabilities will not accommodate. (These
are grouped with the chart to facilitate copy to the report.)

Each chart is is an contiguous region with all of its data and
information.

What I need to do is to create an instance of each chart as it will
appear in the report. By changing global variables, the report
document can be saved and the links broken to produce a correct
variant.

What I am finding tedious and problematic is in producing the charts
in the excel source, when I copy a chart group, the data and
spreadsheet components all remain as relative or absolute references
according to how I set it up, but the chart data, titles and text box
components all remain as absolute references to the original location.
These must be changed manually. Even when the references are entered
as relative, they convert to absolute.

Hopefully someone has a suggestion as to how I might somehow get the
references to change with the copy process. I have tried composite
formulas, range names, and indirect() in the formula bar for these
objects, but only get errors.

Thanks,

Steve

 

[Jon Peltier]

Relative references will not work for these chart elements. What you could
do is make a template of the sheet with its data and chart, then insert a
sheet based on this template for each chart.

- Jon