I tried using OFFSET today in "source data" field for the chart and I get an
error saying "this function is not valid". I copied and pasted the text of
the function into a regular spreadsheet and applied the "COUNT" function to
it and got a valid (and correct) response back, so I'm pretty sure I didn't
make a typo. Here's what I'm trying to enter in the series function for the
chart when I get the error:

SERIES("Manhattan",Data!$C$13:$C$176,OFFSET(Data!$ C$13,0,10,COUNT(Data!$C$13:$C$1000),1),3)

This should use the data in the column 10 columns to the left of C (column
M), starting from row 13 and going down as long as there is data in column C,
which is my category labels in this chart.

This is Excel 2003 if that makes any difference.

Thanks again for your help!
Lee



"Franz Verga" wrote:

Nel post
*lsilverman* ha scritto:

I have a set of charts that are all drawn from a data set that grows
every day. As it is I need to edit the source data settings every
day to extend the charts by a day every time I update the data.

I'd rather set value of the last row to use in one of the cells of the
spreadsheet, and then use a function to return the correct range
instead of having a fixed range. I tried using the ADDRESS() and
INDIRECT() functions but they only refer to a single cell, not a
range. I can't figure out how to do it, and it's probably something
obvious that I'm just not seeing. I'm familiar with VERY rudimentary
macros & defining my own functions if that would help, but I can't
figure out how to get it to return a range instead of a value.

Lee Silverman

An example of dynamic range is:

=OFFSET($B$2,0,0,COUNT($B:$B),1)

I suggest you also to read Chip Pearson's site at this page:

http://www.cpearson.com/excel/named.htm

where he talk about named ranges and expecially of dynamic ranges.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy