Absolutes in a chart

[Jeanne]

I have a workbook that I need to add a row of new data.
This spreadsheet is linked to several different charts in
a separate spreadsheet. The chart spreadsheet has 8
worksheets with each worksheet having 8 charts.

When I insert the row in my first spreadsheet, the charts
change because they have absolute formulas in them. I can
not figure out how to make this an easy change. Hope
someone has a suggestion for me.

Thanks

Jeanne

 

[Jon Peltier]

Jeanne -

Do that charts change because their source range moves down a row? This
is how address references work in Excel, whether absolute or relative.
They stay linked to the same cell, wherever it moves.

You can use dynamic range names to always refer to the same cells. This
Refers To formula defining such a range always looks at cell A3:

=INDIRECT("A3")

This always refers to A1:A10:

=OFFSET(INDIRECT("Sheet1!A1",0,0,10,1)

Without the sheet name, the chart complains.

For more about dynamic charts, see the examples and links here:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______