Dynamic Range

[lsilverman]

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

 

[Franz Verga]

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

 

[lsilverman]

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]

Nel post *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

 

[lsilverman]

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]

Nel post *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

 

[lsilverman]

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