NUmber of Columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Thank you so much to all of you!!
Fast response! Now im sure ill have more questions so please stay tuned if u
can
 
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
 
Back
Top