Chart and Axis Titles relative reference



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

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

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.





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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question