Trick? for using INDIRECT references in Data Source of Chart?

  • Thread starter Thread starter Herb Martin
  • Start date Start date
H

Herb Martin

Is it possible to use INDIRECT references in Data Source of Chart?
If so, is there some trick?

Goal: Chart with several Data ranges which need to be extended
as data is appended to a running list.

I wish to create a cell with the necessary elements to create the
source data reference indirectly.

Or a better way to do this easily....

Thanks
 
Hi,

You can use named ranges on the Data Range tab but the reference will
revert to a static reference once the dialog is closed.

See Tushar's suggestion for specifying named ranges for each individual
series.

Cheers
Andy
 
Andy Pope said:
Hi,

You can use named ranges on the Data Range tab but the reference will
revert to a static reference once the dialog is closed.

See Tushar's suggestion for specifying named ranges for each individual
series.
Click to expand...

I really wanted something where I could just update a single
cell and get all of the (7) ranges to update -- sounds like I
either have to go the VBS route or it just moves the problem
from the chart data to having to rename (all of) the ranges.

Thanks to both of you.
 
You can update a single cell and thus change a load of OFFSETS. For example,
suppose that single cell (A1) contains 1, 2, or 3, referring to which block
of columns to get the X and Y from. The refers-to formulas for X and Y might
look like:

MyXValues
=OFFSET(A1,1,A1*2+2,count(offset(A:A,0,A1*2+2)),1)

MyYValues
=OFFSET(MyXValues,0,1)

Changing the value typed into A1 changes where MyXValues (and therefore
MyYValues) gets its data.

There are many ways to skin this cat, use your imagination.

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


Herb Martin said:
Andy Pope said:
Hi,

You can use named ranges on the Data Range tab but the reference will
revert to a static reference once the dialog is closed.

See Tushar's suggestion for specifying named ranges for each individual
series.
Click to expand...

I really wanted something where I could just update a single
cell and get all of the (7) ranges to update -- sounds like I
either have to go the VBS route or it just moves the problem
from the chart data to having to rename (all of) the ranges.

Thanks to both of you.
Click to expand...
 
Back
Top