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

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<attarkon.ee )



"Gazzr" wrote in
message ...

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

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

Excellent that was just what I needed.

Thanks to all
Garry


--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=544157