In Excel 2010, how can I create a template with a function automatically ends in the last cell with data

microsoft-excel-2010windows

I want to create a template in Excel with a function that includes a range, but the number of cells in this range will change every time the template is applied.

How can I identify that I want the range to start at say, B2 and end at the last cell with text in column B?

Best Answer

This depends on what you're doing, and as such it would help if you added a bit more detail to your question. As it is we can't tell what you're trying to do, or why.

As such I can only advise you on how to create automatic (dynamic) ranges:


1

Use a table - Excel tables are incredibly powerful, and automaticaly count as a range, if you want to refer to every cell in a column just enter [columnname] rather than the traditional cell reference. Excel tables automaticaly expand as new rows are added too, which is pretty handy.

The donwside of tables brings us back to the question of what this template is, and what it's supposed to do: if the tamplate is supposed to be accessed by users of Excel 2003 or earlier tables won't work.


2

Which brings me to option 2: Dynamic Named Ranges.

  • Select the column you want to set with an automatic range by clicking in the first data cell (usally row 2) and go to Formulas>Define Name>Define name

  • Give the name a clear name, such as ColumnNameRange

  • leave Scope as Workbook and add a comment if you want

  • In Refers to: enter this formula (replacing the cell and sheet references as needed)
=OFFSET(SheetName!$A$2,0,0,COUNTA(SheetName!$A$2:$A$500),)


This Dyanmic Named Range, when refered to in a formula, will return everything from the range stated in the offset to the second half of the range stated in the COUNTA. The formula looks at the entire range in the COUNTA to get the number of not blank cells and then creates an offset range selection from the first cell, down to the number of cells found.

More info on Dynamic Named Ranges here: http://www.ozgrid.com/Excel/DynamicRanges.htm
More info on Tables here: http://www.ozgrid.com/Excel/data-tables.htm

A few things to keep in mind RE Dynamic Named Ranges:

  • If you set that second part of the COUNTA to 500 like it is above, and then end up with more than 500 rows of data it won't count the extras, so keep that in mind

  • If there are blanks in the data the COUNTA is looking at then it will only count down to the first blank. To avoid this always set the COUNTA to the first column (which should be your id column so should never have blanks) and then set the cell referenced in the OFFSET to the column you want (so for column D set the COUNTA to $A$2:$A$500 and the OFFSET to $D$2

  • Always fix all the cell ranges with $ (F4) or this won't work properly. All cell ranges should look like this $column$row

  • You can create a Dynamic Named Range that covers a collection of columns by adding another COUNTA after the first: =OFFSET($A$1,0,0,COUNTA($A1:$A500),COUNTA($B1:$B500)). You can do this with tables by either stating some columns: =Table1[[Column1]:[Column2]] or by stating the entire table: =Table1
Related Question