Dynamic Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lsilverman - 25 Jun 2006 23:34 GMT

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

Reply to this Message

Franz Verga - 26 Jun 2006 01:34 GMT

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
[quoted text clipped - 10 lines]
>
> 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.

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

lsilverman - 26 Jun 2006 15:19 GMT

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

> Nel post news:400F6276-31EA-415C-9A4A-B4CA37D2A50B@microsoft.com
> *lsilverman* ha scritto:
[quoted text clipped - 23 lines]
>
> where he talk about named ranges and expecially of dynamic ranges.

Reply to this Message

Franz Verga - 27 Jun 2006 00:50 GMT

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
[quoted text clipped - 14 lines]
> 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...

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

lsilverman - 27 Jun 2006 03:19 GMT

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

> Nel post news:BA209FEE-8D9F-405F-9BCD-83F8EEC7A057@microsoft.com
> *lsilverman* ha scritto:
[quoted text clipped - 23 lines]
> should create a named dynamic range, as from Chip Pearson' site, because
> this is Excel will...

Reply to this Message

Franz Verga - 27 Jun 2006 09:04 GMT

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

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

lsilverman - 27 Jun 2006 14:46 GMT

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/support/samples/chartdefinedname.aspx

Thanks for all your help!

> Nel post news:3513C28E-C908-4297-8980-DC44377BC9E6@microsoft.com
> *lsilverman* ha scritto:
[quoted text clipped - 11 lines]
> 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...

Reply to this Message