Variable data range help

  • Thread starter Thread starter low98
  • Start date Start date
L

low98

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
 
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),COUNTA(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
 
Ed has the right idea about using the OFFSET and COUNTA functions to
create dynamic ranges for charting. 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).

For more see
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
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).
Click to expand...

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