I cant get Excel OFFSET Fnct to return multiple references.
 

I am trying to set up dynamic charts for multiple data arrays, but the OFFSET
function will not return more than one reference. If height or width is not
1, I get a #VALUE error. Has anyone had success from "Using named ranges to
create dynamic charts in Excel" in the excel help files? I am running 2003
sp1 excel.

HI dmkirk,

From my website...

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 items 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))

Ed Ferrero
http://edferrero.m6.net/

I am trying to set up dynamic charts for multiple data arrays, but the
OFFSET
function will not return more than one reference. If height or width is
not
1, I get a #VALUE error. Has anyone had success from "Using named ranges
to
create dynamic charts in Excel" in the excel help files? I am running
2003
sp1 excel.

It would be helpful if you post the formulas you used to define your names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


dmkirk wrote:

I am trying to set up dynamic charts for multiple data arrays, but the OFFSET
function will not return more than one reference. If height or width is not
1, I get a #VALUE error. Has anyone had success from "Using named ranges to
create dynamic charts in Excel" in the excel help files? I am running 2003
sp1 excel.