+ Reply to Thread
Results 1 to 3 of 3

I cant get Excel OFFSET Fnct to return multiple references.

  1. #1
    dmkirk
    Guest

    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.

  2. #2
    Ed Ferrero
    Guest

    Re: I cant get Excel OFFSET Fnct to return multiple references.

    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),COUNTA(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.




  3. #3
    Jon Peltier
    Guest

    Re: I cant get Excel OFFSET Fnct to return multiple references.

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1