Changing named ranges

[Gazzr]

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

 

[Arvi Laanemets]

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)

 

[Guest]

Take a look at http://www.contextures.com/xlNames01.html about dynamically
named ranges.

 

[Gazzr]

Excellent that was just what I needed.

Thanks to all
Garry