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