If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Dynamic Range
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 |
#3
|
|||
|
|||
Dynamic Range
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 |
#4
|
|||
|
|||
Dynamic Range
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 |
#5
|
|||
|
|||
Dynamic Range
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 *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 |
#6
|
|||
|
|||
Dynamic Range
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 |
#7
|
|||
|
|||
Dynamic Range
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/suppo...finedname.aspx Thanks for all your help! "Franz Verga" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dynamic range | Hans Knudsen | Charts and Charting | 1 | May 17th, 2006 07:26 AM |
Conditional Formatting & Dynamic Range | james | Worksheet Functions | 2 | October 11th, 2005 04:25 PM |
Reference to a dynamic range | Yossi | General Discussion | 2 | April 12th, 2005 12:57 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
problems with dynamic range names and shared workbooks | JulieD | General Discussion | 4 | August 11th, 2004 02:13 PM |