Dynamic Range

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

Nel post news:400F6276-31EA-415C-9A4A-B4CA37D2A50B@microsoft.com
*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

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 news:400F6276-31EA-415C-9A4A-B4CA37D2A50B@microsoft.com
> *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
>
>
>

Nel post news:BA209FEE-8D9F-405F-9BCD-83F8EEC7A057@microsoft.com
*lsilverman* ha scritto:

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

Hi Lee,

instead of using the OFFSET function directly inside the SERIES one, you
should create a named dynamic range, as from Chip Pearson' site, because
this is Excel will...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Tried using a named range, but no luck. I downloaded the example sheet from
Pearson's web site, and tried to graph the dynamic ranges in his spreadsheet,
but also didn't succeed. Perhaps you can't use named ranges as source data
for a chart in Excel?

Lee

"Franz Verga" wrote:

> Nel post news:BA209FEE-8D9F-405F-9BCD-83F8EEC7A057@microsoft.com
> *lsilverman* ha scritto:
>
> > 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
> >
>
> Hi Lee,
>
> instead of using the OFFSET function directly inside the SERIES one, you
> should create a named dynamic range, as from Chip Pearson' site, because
> this is Excel will...
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>

Nel post news:3513C28E-C908-4297-8980-DC44377BC9E6@microsoft.com
*lsilverman* ha scritto:

> Tried using a named range, but no luck. I downloaded the example
> sheet from Pearson's web site, and tried to graph the dynamic ranges
> in his spreadsheet, but also didn't succeed. Perhaps you can't use
> named ranges as source data for a chart in Excel?
>
> Lee

I think I remember you can use named ranges in charta, but you have also to
use the nameof workbook, e.g. if you have a named range "myrange" (without
quotes) in a workbook named "myWB.xls", so in the SERIES function you have
to input as [myWB.xls]myrange or something like this... Try also with the
sheet name, because I'm not sure of how it was...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

I figured it out thanks to your help. In my case the data are stored in a
worksheet named data, in a file called 'Sales Metrics.xls'. I Have named
ranges like Mprice for average selling price.

In the Source Data dialog or the SERIES definition, you can enter
='Sales Metrics.xls'!Mprice

To refer to the named range MPrice.

I also found a web site with an example spreadsheet:

http://www.spreadsheetgear.com/supp...efinedname.aspx

Thanks for all your help!


"Franz Verga" wrote:

> Nel post news:3513C28E-C908-4297-8980-DC44377BC9E6@microsoft.com
> *lsilverman* ha scritto:
>
> > Tried using a named range, but no luck. I downloaded the example
> > sheet from Pearson's web site, and tried to graph the dynamic ranges
> > in his spreadsheet, but also didn't succeed. Perhaps you can't use
> > named ranges as source data for a chart in Excel?
> >
> > Lee
>
> I think I remember you can use named ranges in charta, but you have also to
> use the nameof workbook, e.g. if you have a named range "myrange" (without
> quotes) in a workbook named "myWB.xls", so in the SERIES function you have
> to input as [myWB.xls]myrange or something like this... Try also with the
> sheet name, because I'm not sure of how it was...
>
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>