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

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]

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

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

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

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

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

"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

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

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