NUmber of Columns

[M&M]

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?

 

[Toppers]

=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

 

[Peo Sjoblom]

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

 

[Don Guillett]

Use a defined named range that is self adjusting
insert>name>define>name 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

 

[M&M]

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

 

[challa prabhu]

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

 

[Don Guillett]

Not necessary with a defined name range as already suggested.