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