Dynamic Named Range in Excel

A dynamic named range is automatically expanded when a value is added to the range.

1. For example, select a range A1: A4 and give it a name Prices.
2. Calculate the amount.
3. When you add a value to a range, Excel doesn’t update the sum.

To automatically expand a named range when you add a value, follow these few steps:

4. On the Advanced tab Formulas (Formulas) select Name Manager (name manager).
5. Нажмите кнопку Edit (Change).
6. Click on the field Refer to (Range) and enter the formula:

   =OFFSET($A$1,0,0,COUNTA($A:$A),1)

   =СМЕЩ($A$1;0;0;СЧЕТЗ($A:$A);1)

   

   Explanation: Function OFFSET (OFFSET) takes 5 arguments:

   • link: $ A $ 1,
   • line offset: 0,
   • column offset: 0,
   • Height: COUNTA($A:$A) or COUNT($A:$A),
   • width: 1.

   The formula COUNTA($A:$A) or COUNTA($A:$A) counts the number of values ​​in column A. When you add a value to a range, the number of elements increases. As a result, the named range is expanded.

7. Press OK, and then Fermer (Close).
8. Now, when you add a value to a range, Excel automatically updates the sum.