Creating a Dynamic Range

Spreadsheets often do not remain a fixed size; the number of rows will increase and decrease as records are added or deleted. This can cause problems if it affects a named range that is used in subsequent formulas. To avoid having to edit your named ranges every time you add or remove a row or column, you can create what is known as a dynamic range. A dynamic range is a named range that uses a dynamic reference so that it will expand automatically as new items are added to the range, therefore giving any formulas dependent on it more flexibility. You can create a dynamic range by using the OFFSET function that defines the range size based on the number of items in the column, which is calculated using a COUNT or COUNTA function.

1. Open the New Name dialog box by clicking Name a Range in the Defined Names group or by clicking New in the Name Manager dialog box.
2. Enter a name for the range in the Name box, select a scope in the Scope box, and add a description in the Comment box as appropriate.
3. In the Refers to box, enter a dynamic reference using the following format as a guide:
   =OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A),1)
   where the list is on Sheet1 starting at cell A1 and the arguments are:
   
   1. Reference cell: Sheet1!$A$1
   2. Rows to offset: 0
   3. Columns to offset: 0
   4. Number of rows: COUNTA($A:$A)
   5. Number of columns: 1

If you want the number of columns to be dynamic, replace 1 with COUNTA($1$1).