How do I insert a formula into chart source data values field box?

[tiptoe]

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

 

[Del Cotter]

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".

 

[tiptoe]

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,

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

 

[Jon Peltier]

Here are some examples which may help:

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

- Jon