Re: Changing named ranges

Giganews Newsgroups
Subject: Re: Changing named ranges
Posted by:  Arvi Laanemets (garba…@hot.ee)
Date: Mon, 22 May 2006

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" <Gazzr.2870yy_1148274901.0924@excelforum-nospam.com> wrote in
message news:Gazzr.2870yy_1148274901.0924@excelforum-nospam.com...
>
> 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?action=getinfo&userid=31075
> View this thread:http://www.excelforum.com/showthread.php?threadid=544157

Replies

None

In response to

Changing named ranges posted by Gazzr on Mon, 22 May 2006