+ Reply to Thread
Results 1 to 4 of 4

Changing named ranges

  1. #1
    Registered User
    Join Date
    02-01-2006
    Posts
    60

    Changing named ranges

    Hi there,

    Consider an example where I have Defined a named range called "garry" which covers cells A1:E5. How can I get excel to update/change the named range to A1:E10 if I paste five more rows of data into the sheet.

    I want to do this without going into Insert > Name > Define etc and changing it manually.

    Hope all that made sense.

    Thanks
    Gazzr

  2. #2
    Arvi Laanemets
    Guest

    Re: Changing named ranges

    Hi

    Define the named range as a dynamic one, p.e.

    =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),1)

    This assumes, you don't have any entries in column A, except your list (or
    only some fixed number of other entries, p.e. column heading), and you never
    have any gaps in this range.

    An example of definition for same range, when entries start from row 3, and
    you have a heading in cell A1:
    =OFFSET(Sheet1!$A$1,2,,COUNTA(Sheet1!$A:$A)-1,1)


    --
    Arvi Laanemets
    ( My real mail address: arvi.laanemets<at>tarkon.ee )



    "Gazzr" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi there,
    >
    > Consider an example where I have Defined a named range called "garry"
    > which covers cells A1:E5. How can I get excel to update/change the
    > named range to A1:E10 if I paste five more rows of data into the
    > sheet.
    >
    > I want to do this without going into Insert > Name > Define etc and
    > changing it manually.
    >
    > Hope all that made sense.
    >
    > Thanks
    > Gazzr
    >
    >
    > --
    > Gazzr
    > ------------------------------------------------------------------------
    > Gazzr's Profile:
    > http://www.excelforum.com/member.php...o&userid=31075
    > View this thread: http://www.excelforum.com/showthread...hreadid=544157
    >




  3. #3
    MarkN
    Guest

    RE: Changing named ranges

    Take a look at http://www.contextures.com/xlNames01.html about dynamically
    named ranges.
    --
    hope this helps,
    MarkN


    "Gazzr" wrote:

    >
    > Hi there,
    >
    > Consider an example where I have Defined a named range called "garry"
    > which covers cells A1:E5. How can I get excel to update/change the
    > named range to A1:E10 if I paste five more rows of data into the
    > sheet.
    >
    > I want to do this without going into Insert > Name > Define etc and
    > changing it manually.
    >
    > Hope all that made sense.
    >
    > Thanks
    > Gazzr
    >
    >
    > --
    > Gazzr
    > ------------------------------------------------------------------------
    > Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
    > View this thread: http://www.excelforum.com/showthread...hreadid=544157
    >
    >


  4. #4
    Registered User
    Join Date
    02-01-2006
    Posts
    60

    Thanks!

    Excellent that was just what I needed.

    Thanks to all
    Garry

+ 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