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

--
Herb Martin, MCSE, MVP
Accelerated MCSE
http://www.LearnQuick.Com
[phone number on web site]

See
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

If you do use a method other than OFFSET, remember to include the worksheet
name in your named formula. That *should* work; no promises, though.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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

Herb Martin wrote:
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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

"Andy Pope" wrote in message
...
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.

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.

--
Herb Martin, MCSE, MVP
Accelerated MCSE
http://www.LearnQuick.Com
[phone number on web site]


Cheers
Andy

Herb Martin wrote:
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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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" wrote in message
...
"Andy Pope" wrote in message
...
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.

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.

--
Herb Martin, MCSE, MVP
Accelerated MCSE
http://www.LearnQuick.Com
[phone number on web site]


Cheers
Andy

Herb Martin wrote:
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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info