Create a dynamic named range on a worksheet

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002

You can create a dynamic named range that extends to include new information by using the formulas in the following examples.

 Note   The formula in each example assumes that you are extending the range up to 200 rows of data. You can revise the defined names so that they use the appropriate number by changing the ending cell reference in the COUNTA or COUNT function to reflect the maximum number of rows.

Example 1: Create a dynamic named range that contains text

Use the OFFSET function with the COUNTA function to do this task.

  1. Type data like the following into your worksheet.
A B
1 Month Sales
2 Jan 10
3 Feb 20
4 Mar 30
  1. On the Insert menu, point to Name, and then click Define.
  2. In the Names in workbook box, type Date.
  3. In the Refers to box, type the following formula.
=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
  1. Click OK.

Example 2: Create a dynamic named range that contains numbers

Use the OFFSET function with the COUNT function to do this task.

  1. Type data like the following into your worksheet.
A B
1 Month Sales
2 Jan 10
3 Feb 20
4 Mar 30
  1. On the Insert menu, point to Name, and then click Define.
  2. In the Names in workbook box, type Sales.
  3. In the Refers to box, type the following formula.
=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)
  1. Click OK.
  2. Clear cell B2, and then type the following formula.
=RAND()*0+10

 Note   This formula uses the volatile RAND function. This formula automatically updates the OFFSET formula that is used in the defined name "Sales" when you enter new data in column B. The value 10 is used in this formula because it is the original value of cell B2.

 
 
Applies to:
Excel 2003