Sometimes user may in need where he wants to resize the range of a formula dynamically based on data addition or deletion. In this article we will see how we can resize a name range.
Step 1: Consider following data where we will count number of name available in the column using COUNTA function
Step 2: Select the name from “A1:A9” and do followings to define a name range:
- Navigate Formulas tab
- Click on Name Managers under Defined names group
- Click on New button on the Name Manager dialog
- Give the Name as “NameList” and say OK
- Then close the dialog
Step 3: Write COUNTA formula to get the static count as shown below:
Result would look like below:
So far we have created a COUNT based on static Name range, lets make it dynamic.
Step 4: Click on Name Manager under Formulas tab and edit the name range “NameList” by clicking on Edit button and put formula as shown below:
Step 5: Now try to add or delete values in Column “A” and see the Number of Names column value auto gets changes: