Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Variable data range help
I'm looking to build a variable data range to help in some charting. I've tried a few forumla/text combos and have had no luck thus far. I'm wondering what is exceptable to use in the data range feild. Is the string I've tried. Basically I'm looking to create an open ended data range. Any help?? ='Chart Info'!$H$40:$I$"&COUNT(I:I)+39&",'Chart Info'!$M$40:$N$"&COUNT(I:I)+39 Thanks! Ryan -- low98 ------------------------------------------------------------------------ low98's Profile: http://www.excelforum.com/member.php...o&userid=30316 View this thread: http://www.excelforum.com/showthread...hreadid=499794 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Variable data range help
Hi low98,
How to use a dynamic range. Suppose your data is in the range "A1:G19", then define a new range name called AcData with the following formula; =Sheet1!$A$1:INDEX(Sheet1!$G:$G, COUNTA(Sheet1!$A:$A)) This assumes that column headings are in row 1, and that column A contains a value for every row in the data range ie no null values or blanks. The dynamic range works because COUNTA(Sheet1!$A:$A) gives the total number of rows, and INDEX(Reference,RowNo) points to the cell in 'Reference' given by 'RowNo'. In our case 'Reference' is all of column G, so if there are values in A1 to A19, the INDEX function would point to cell G19. To add a named range, use the menu item Insert-Name-Define... enter the name for the range and the formula, then click Ok. Similar to the above, you can use the OFFSET function to define a dynamic range. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6) This is nice because you can easily make the range dynamic in both dimensions. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) If you get tired of entering long OFFSET and COUNTA functions, download 'EFutil.zip' from http://www.edferrero.com/vba.aspx This is an add-in that lets you create dynamic ranges just by selecting the range. Ed Ferrero Microsoft Excel MVP http://www.edferrero.com I'm looking to build a variable data range to help in some charting. I've tried a few forumla/text combos and have had no luck thus far. I'm wondering what is exceptable to use in the data range feild. Is the string I've tried. Basically I'm looking to create an open ended data range. Any help?? ='Chart Info'!$H$40:$I$"&COUNT(I:I)+39&",'Chart Info'!$M$40:$N$"&COUNT(I:I)+39 Thanks! Ryan -- low98 ------------------------------------------------------------------------ low98's Profile: http://www.excelforum.com/member.php...o&userid=30316 View this thread: http://www.excelforum.com/showthread...hreadid=499794 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Variable data range help
Hi Tushar,
However, AFAIK, there is no way to use a range that contains multiple columns *and* multiple rows in a chart. It has to be as single column (or row). Quite right, I had not thought of that. I mostly use dynamic ranges with multiple rows and columns in Pivot Tables and Pivot Charts, where they are very useful. If you add a new column to the data, you just need to refresh the pivot chart to see a new field in the field list. Ed Ferrero |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation range | Excel Discussion (Misc queries) | |||
Dynamic Data Range | Excel Worksheet Functions | |||
How to create a chart based on a 2 dim data range dynamical in 1 d | Charts and Charting in Excel | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |