Intelligent named lists

Vertical lists

You can define a named list that will automatically expand to include new items added to the end of the list. There mustn't be any blanks in the list.

Go to Insert - Name - Define and then type in a name for the list in the box at the top. Then enter the following formula into the Refers to: box:

=OFFSET(TopCellOfList, 0, 0, COUNTA(TopCellOfList:EndOfCellList),1)

where

TopCellOfList is the cell location of the top value in the list, such as $C$5.

EndOfCellList is the location of lowest possible item in the list, such as $C$10000.

Click on Add to finish.

Horizontal lists

Use the following formula for a horizontal list (there mustn't be any blanks in the list):

=OFFSET(LeftMostCell, 0, 0, 1, COUNTA(LeftMostCell:RightMostCell))

where

LeftMostCell is the cell location at the extreme left of the list, such as $D$23.

RightMostCell is the location of the rightmost possible item in the list, such as $AA$23.

Click on Add to finish.

Rectangular regions

Use the following formula for a rectangular region (there mustn't be any blanks in the left-hand column or the top row):

=OFFSET(TopLeftCell,0,0,COUNTA(TopLeftCell:BottomLeftCell), COUNTA(TopLeftCell:TopRightCell))

where

TopLeftCell is the cell location at the extreme left of the list, such as $D$23.

TopRightCell is the cell location at the rightmost possible location of an item in the list, such as $AA$23.

BottomLeftCell is the location of the lowest possible item in the list, such as $D$10000.

Click on Add to finish.