If I have a named range for instance $A$11 : $C$14 = Range("TEST")

and I want to add any more values to this range how can i make it so that the
user can add more values which are outside this range and have the range
automatically change to capture the newer values as well in the same named
range("TEST")

You can set up a dynamic named range which automatically adjusts if
you add more values to it - Chip Pearson explains how he

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

and Debra Dalgleish also explains this he

http://www.contextures.com/xlNames01.html#Dynamic

Hope this helps.

Pete

On Jul 27, 11:48 pm, N.F wrote:
If I have a named range for instance $A$11 : $C$14 = Range("TEST")

and I want to add any more values to this range how can i make it so that the
user can add more values which are outside this range and have the range
automatically change to capture the newer values as well in the same named
range("TEST")

One can't "add" to a name's referenced range. Instead you just assign a
new (expanded) range to the name.

How do you propose XL should know what range the user wants to include?

In article ,
N.F wrote:

If I have a named range for instance $A$11 : $C$14 = Range("TEST")

and I want to add any more values to this range how can i make it so that the
user can add more values which are outside this range and have the range
automatically change to capture the newer values as well in the same named
range("TEST")

Thanks Pete thats exactly what I needed.

JE McG.... it seems there is a way JEMcG one can use the Offset command or
even make a macro to expand the range if there is a value in the cell.

"Pete_UK" wrote:

You can set up a dynamic named range which automatically adjusts if
you add more values to it - Chip Pearson explains how he

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

and Debra Dalgleish also explains this he

http://www.contextures.com/xlNames01.html#Dynamic

Hope this helps.

Pete

On Jul 27, 11:48 pm, N.F wrote:
If I have a named range for instance $A$11 : $C$14 = Range("TEST")

and I want to add any more values to this range how can i make it so that the
user can add more values which are outside this range and have the range
automatically change to capture the newer values as well in the same named
range("TEST")

Glad to be of help - thanks for feeding back.

Pete

On Jul 28, 12:24 am, N.F wrote:
Thanks Pete thats exactly what I needed.

JE McG.... it seems there is a way JEMcG one can use the Offset command or
even make a macro to expand the range if there is a value in the cell.



"Pete_UK" wrote:
You can set up a dynamic named range which automatically adjusts if
you add more values to it - Chip Pearson explains how he

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

and Debra Dalgleish also explains this he

http://www.contextures.com/xlNames01.html#Dynamic

Hope this helps.

Pete

On Jul 27, 11:48 pm, N.F wrote:
If I have a named range for instance $A$11 : $C$14 = Range("TEST")

and I want to add any more values to this range how can i make it so that the
user can add more values which are outside this range and have the range
automatically change to capture the newer values as well in the same named
range("TEST")- Hide quoted text -

- Show quoted text -

Yup. I was focused on your problem statement, rather than on what you
likely were looking for.

A calculated, or dynamic, range which uses a *formula* as the definition
of the range can certainly automatically adjust, and, rereading your
original post, it's obvious that's the most likely explanation of what
you were looking for, and I'm not sure why I missed it.

OTOH, you if you actually assign a name to a range, the way you
indicated in your original post, one cannot add to it, one can only
reassign the reference to the name.

Note, a macro can't expand the range. It can only reassign the reference.

In article ,
N.F wrote:

JE McG.... it seems there is a way JEMcG one can use the Offset command or
even make a macro to expand the range if there is a value in the cell.