+ Reply to Thread
Results 1 to 4 of 4

How to define a "from this row to the bottom of the spreadsheet"range?

  1. #1
    Ney André de Mello Zunino
    Guest

    How to define a "from this row to the bottom of the spreadsheet"range?

    Hello.

    In a spreadsheet, I have a formula which refers to the values of a
    column ('C', for instance). The first line of that column is a title, so
    I currently use a range that begins with 'C2'. There is no total at the
    bottom of the column. All cells that follow the last value are blank.

    The problem is that the number of values on that column grows
    periodically. As of now, I am using an arbitrary high value to make up
    the range in the formula, e.g. 'C2:C1000'. The question is: how can I
    specify that range without using a magic number? In other words, how
    does one define a range that means: from this row to the bottommost row.

    Thank you,

    --
    Ney André de Mello Zunino

  2. #2
    Gord Dibben
    Guest

    Re: How to define a "from this row to the bottom of the spreadsheet" range?

    Perhaps a Dynamic Range would suit your purposes.

    It expands and contracts with the change of entries.

    See Debra Dalgleish's site for instruction on creating Dynamic Ranges and naming
    them

    http://www.contextures.on.ca/xlNames01.html#Dynamic


    Gord Dibben MS Excel MVP

    On Wed, 21 Jun 2006 19:49:11 -0300, Ney André de Mello Zunino
    <zunino@inf.ufsc.br> wrote:

    >Hello.
    >
    >In a spreadsheet, I have a formula which refers to the values of a
    >column ('C', for instance). The first line of that column is a title, so
    >I currently use a range that begins with 'C2'. There is no total at the
    >bottom of the column. All cells that follow the last value are blank.
    >
    >The problem is that the number of values on that column grows
    >periodically. As of now, I am using an arbitrary high value to make up
    >the range in the formula, e.g. 'C2:C1000'. The question is: how can I
    >specify that range without using a magic number? In other words, how
    >does one define a range that means: from this row to the bottommost row.
    >
    >Thank you,



  3. #3
    Harlan Grove
    Guest

    Re: How to define a "from this row to the bottom of the spreadsheet" range?

    Ney Andr=E9 de Mello Zunino wrote...
    ..=2E.
    >specify that range without using a magic number? In other words, how
    >does one define a range that means: from this row to the bottommost row.

    ..=2E.

    You could define a name like C.HD ([col] C here down) referring to the
    formula

    =3DINDEX($C:$C,N(ROW())):INDEX($C:$C,ROWS($C:$C))


  4. #4
    Harlan Grove
    Guest

    Re: How to define a "from this row to the bottom of the spreadsheet" range?

    Harlan Grove wrote...
    ....
    >=INDEX($C:$C,N(ROW())):INDEX($C:$C,ROWS($C:$C))


    The N call was unnecessary. Simplify it to

    =INDEX($C:$C,ROW()):INDEX($C:$C,ROWS($C:$C))


+ 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