My ultimate aim is to build a chart that refers to values in a range
of cells held in a row on another worksheet. Normally this would be
straightforward by manually editing the source data in the chart and
pointing to the range of cells. However, over time the range of cells
used to provide the values for the chart will change and I am looking
for a method that will do this automatically.

I thought if I could produce a formula that identifies the cells when
the change I could simply paste that formula into the Chart's source
data values field. Of course, I've tried it but it will not accept it.

For example, where the source worksheet is named Data Sheet(2) and the
range of cell are $AI$8:$BB$8 I've come up with a formula that returns
the cell range:

=ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,35) & ":"&
ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,54)

This returns: $AI$8:$BB$8

Pasting the formula in the chart source data values field did not
work, I then tried:

= 'Data Sheet'! & ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!
AH7:AH18,0)+6,35) & ":"& ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!
AH7:AH18,0)+6,54)

No luck with this either.

Any suggestions?

Bob
Nottingham UK

On Sat, 16 Feb 2008, in microsoft.public.excel.charting,
tiptoe said:

My ultimate aim is to build a chart that refers to values in a range
of cells held in a row on another worksheet. Normally this would be
straightforward by manually editing the source data in the chart and
pointing to the range of cells. However, over time the range of cells
used to provide the values for the chart will change and I am looking
for a method that will do this automatically.

Excel source data boxes won't accept formulas, but they will accept
named ranges, which can be formulas. So just create a named formula that
evaluates to a range (not just to a single value).

The formula that will do this for you (one that has as its output a
whole range of cells, not just a value) is OFFSET().

For more information on making charts that change using named ranges and
OFFSET(), Google "Excel Dynamic Charts".

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

On Feb 16, 11:07 pm, Del Cotter wrote:
On Sat, 16 Feb 2008, in microsoft.public.excel.charting,
tiptoe said:

My ultimate aim is to build a chart that refers to values in a range
of cells held in a row on another worksheet. Normally this would be
straightforward by manually editing the source data in the chart and
pointing to the range of cells. However, over time the range of cells
used to provide the values for the chart will change and I am looking
for a method that will do this automatically.

Excel source data boxes won't accept formulas, but they will accept
named ranges, which can be formulas. So just create a named formula that
evaluates to a range (not just to a single value).

The formula that will do this for you (one that has as its output a
whole range of cells, not just a value) is OFFSET().

For more information on making charts that change using named ranges and
OFFSET(), Google "Excel Dynamic Charts".

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Del,

Thanks, I'll see if I can come up with and OFFSET() formula that
returns the required range.

Here are some examples which may help:

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

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


"tiptoe" wrote in message
...
On Feb 16, 11:07 pm, Del Cotter wrote:
On Sat, 16 Feb 2008, in microsoft.public.excel.charting,
tiptoe said:

My ultimate aim is to build a chart that refers to values in a range
of cells held in a row on another worksheet. Normally this would be
straightforward by manually editing the source data in the chart and
pointing to the range of cells. However, over time the range of cells
used to provide the values for the chart will change and I am looking
for a method that will do this automatically.

Excel source data boxes won't accept formulas, but they will accept
named ranges, which can be formulas. So just create a named formula that
evaluates to a range (not just to a single value).

The formula that will do this for you (one that has as its output a
whole range of cells, not just a value) is OFFSET().

For more information on making charts that change using named ranges and
OFFSET(), Google "Excel Dynamic Charts".

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

Del,

Thanks, I'll see if I can come up with and OFFSET() formula that
returns the required range.