Creating Dynamic Range Names with OFFSET

Another great use of the OFFSET function is to create a dynamic range name, one that expands or contracts depending on the number of data values in a range. Consider the example to the right, where monthly sales values are entered in column L$,$ and the total of all sales values is calculated in the orange cell. Every month, an extra sales value and its month label are appended to the list in columns K and L$.$ You can make the formula in the orange cell automatically adjust to the appended values by using a dynamic range name.

To do this, select the Define Name dropdown in the Formulas ribbon. In the Name box at the top of the resulting dialog box, enter Sales as the range name. In the Refers To box at the bottom, enter the formula

$=$OFFSET$($$L$$57,0,0,$COUNTA$($$L:$L$)-$COUNTA$($$L$$1$:$L$$56),1).$

$($See to the right.$)$ Then click OK$.$ Finally, enter the formula $=$SUM$($Sales$)$ in the orange cell. Note that COUNTA$($$L:$L$)$ refers to the count of all nonblank cells in column L$,$ and COUNTA$($$L$$1$:$L$$56)$ refers to all the nonblank cells in column L above the Sales data. So the difference is the number of sales values in column L$.$ Therefore, this OFFSET function refers to a range that starts in cell L$57$ and has as many rows as there are sales values in column L$.$ To see how it adjusts, enter a sales value for June below the May row and watch how the total changes automatically.

Again, note that COUNTA$($$L:$L$)$ counts all nonblank values in column L$,$ so if there were some other numbers down below sales, they would mess up the logic in the OFFSET function. For example, suppose there were a numeric value in cell L$200.$ Then the fourth argument in the OFFSET function would return $6,$ not $5,$ so at that point, the Sales range name would refer to the range L$57$:L$62,$ that is$,6$ cells starting with L$57.$ The moral is that if you want to use dynamic range names, you have to be careful about $$junk$$ in the affected columns or rows.

Try it$!$ The data way out to the right has sales by month and by salesperson. You want to sum sales over all months and salespeople in the tan cell. Create a dynamic range name that updates when new months are added and when salesperson columns are added or deleted. Then use this range name to sum sales in the tan cell. $($Note that this example has been placed way out to the right so that no "junk" from the previous examples on this worksheet are above it$.$ This makes the dynamic range formula a bit easier.$)$