Naming data ranges

Hi Melanie

Thank you for your question.

If your range spans several rows (for example from rows A1 to A7) and a new item is added in row A8 we can make the range automatically include the new row in the range.

Highlight the values in the column to be included in the range and use Insert > Name > Define and fill in the name box to describe your range e.g. My_Range. Before you click OK in the Refers to section put the following:

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

This uses OFFSET and COUNTA functions.
Click OK to complete naming the range.

Now we'll test it. Back in your sheet in a new cell set up a simple function to count how many items there are in your range.

=count(My_Range)
The result in this in this case would be 7

Now add a new item on the next row belowthe values in column A. When you press enter the count should increase by 1 as the new row is included in the range.

I hope this helps - do let us know if you have any further questions.

Kind regards,
Andrew