In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named range. This makes the named range dynamic meaning you don´t need to adjust cell references every time you add a new row or column to the list. The formula takes care of only one list per sheet.

How to create a named range in excel 2007

  1. Click "Formulas" tab on the ribbon.
  2. Click "Name Manager".
  3. Create a new named range and name it.
  4. Type the formula below in "Refers to:" window:
  5. Click close button.

Named range forumla:

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

Explaining formula

Step 1 - Count the number of cells in column A that are not empty

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

becomes

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, 4, COUNTA(Sheet1!$1:$1))

Step 2 - Count the number of cells in row 1 that are not empty

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

becomes

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, 4, 3)

Step 3 - Return a reference of the cell at the intersection of a particular row and column

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

becomes

=Sheet1!$A$1:$C$4

This cell reference changes whenever new rows or columns are added or removed.

Possible scenarios when to use named ranges

Functions in this article:

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range