Hi, I have a named range in which I have some values. However this range will
be changing all the time. Maybe will increase in number of colums and rows
maybe decrease depending on the inputs of the user.

How can I make it so that the range remains named "Data" and changes in size
when the user inputs values?

=OFFSET(summary!$A1,,,COUNTA(summary!$A:$A),COUNTA (summary!$1:$1))

This works PROVIDING cells in Column A and row 1 have no embedded blanks.
Also assumes A is longest column.

Change "Summary" as needed


"M&M" wrote:

Hi, I have a named range in which I have some values. However this range will
be changing all the time. Maybe will increase in number of colums and rows
maybe decrease depending on the inputs of the user.

How can I make it so that the range remains named "Data" and changes in size
when the user inputs values?

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


--
Regards,

Peo Sjoblom


"M&M" wrote in message
...
Hi, I have a named range in which I have some values. However this range
will
be changing all the time. Maybe will increase in number of colums and rows
maybe decrease depending on the inputs of the user.

How can I make it so that the range remains named "Data" and changes in
size
when the user inputs values?

Use a defined named range that is self adjusting
insertnamedefinename it as desired such as myrnge
in the refers to use something like this
=offset($a$1,0,0,counta($a:$a),12)
look in the help index for OFFSET for more info

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"M&M" wrote in message
...
Hi, I have a named range in which I have some values. However this range
will
be changing all the time. Maybe will increase in number of colums and rows
maybe decrease depending on the inputs of the user.

How can I make it so that the range remains named "Data" and changes in
size
when the user inputs values?

Thank you so much to all of you!!
Fast response! Now im sure ill have more questions so please stay tuned if u
can

"Don Guillett" wrote:

Use a defined named range that is self adjusting
insertnamedefinename it as desired such as myrnge
in the refers to use something like this
=offset($a$1,0,0,counta($a:$a),12)
look in the help index for OFFSET for more info

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"M&M" wrote in message
...
Hi, I have a named range in which I have some values. However this range
will
be changing all the time. Maybe will increase in number of colums and rows
maybe decrease depending on the inputs of the user.

How can I make it so that the range remains named "Data" and changes in
size
when the user inputs values?

Hi,

Assuming that you have a range containing two rows and two columns and you
have named the range as DATA. Now when you add rows or columns, you must add
those columns and Rows within the named range.

This way the "Refer to:" text box, in the Define Name dialog will
automatically take the relative refernce of your modified cell range.

Challa Prabhu

"M&M" wrote:

Hi, I have a named range in which I have some values. However this range will
be changing all the time. Maybe will increase in number of colums and rows
maybe decrease depending on the inputs of the user.

How can I make it so that the range remains named "Data" and changes in size
when the user inputs values?

Not necessary with a defined name range as already suggested.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"challa prabhu" wrote in message
...
Hi,

Assuming that you have a range containing two rows and two columns and
you
have named the range as DATA. Now when you add rows or columns, you must
add
those columns and Rows within the named range.

This way the "Refer to:" text box, in the Define Name dialog will
automatically take the relative refernce of your modified cell range.

Challa Prabhu

"M&M" wrote:

Hi, I have a named range in which I have some values. However this range
will
be changing all the time. Maybe will increase in number of colums and
rows
maybe decrease depending on the inputs of the user.

How can I make it so that the range remains named "Data" and changes in
size
when the user inputs values?