How to create a dynamic defined range in an Excel worksheet (830287)

SUMMARY

In Microsoft Excel, you may have a named range that must be extended to include new information. This article describes a method to create a dynamic defined name.

Note The method in this article assumes that there are no more than 200 rows of data. You can revise the defined names so that they use the appropriate number and reflect the maximum number of rows.
back to the top

Use the OFFSET Formula with a Defined Name

To use this method, follow these steps:

1. In a new worksheet, type the following data:

   	A	B
   1	Month	Sales
   2	Jan	10
   3	Feb	20
   4	Mar	30

2. On the Insert menu, point to Name, and then click Define.
3. In the Names in workbook box, type Date.
4. In the Refers to box, type: =OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1).
5. Click Add.
6. In the Names in workbook box, type Sales.
7. In the Refers to box, type: =OFFSET($B$2,0,0,COUNT($B$2:$B$200),1).
8. Click OK.
9. Clear cell B2, and then type the following formula: =RAND()*0+10.
   
   Note In this formula, COUNT is used for a column of numbers, while COUNTA is used for a column of text values.
   
   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.

back to the top