OFFSET example - create a dynamic range
Create a name to refer to a dynamic range
It may be the case that you need to refer to a range, and you'd like to have a meaningful name for the range, but you also want the name to include any new entries that are typed onto the end of the range.
That is possible by using a formula to define the range your name refers to. Such a formula is shown in the graphic for this section ... it uses OFFSET and COUNTA in combination, to calculate how many cells the name should refer to.
The formula is this:
=OFFSET(Sh_x!$A$1,1,0,COUNTA(Sh_x!$A:$A)-1,1)
.... the various parts of the formula are the OFFSET function itself, of course, and it's arguments/parameters:
Sh_x!$A$1, :- the anchor point, the start cell of the offset calculation
1,0, :- the offset amounts, being 1 row and 0 columns. 1 row is specified as we want to skip the title cell
COUNTA(Sh_x!$A:$A)-1, :- the length, in rows, of how long the reference is to be calculated as. The COUNTA function counts all the non-blank cells in the column, and -1 is applied as we don't want to count the title cell.
1) :- the final argument/parameter is a 1 ... to indicate that we require a reference that covers only 1 column.
More information
The name created can be used in almost any Excel function or action, such as being a PivotTable source area ... very useful if you have changing data, as the reference recalculates automatically, so that a simple PivotTable refresh pulls in all the required data.
Beware of users typing anything that is non-data in the column of the defined data, as that will cause an inaccurate range definition. Likewise, do not allow or use any blank entries in the data range, for the same reason.