Lots of companies haven't upgraded to xl2003. So using relying on this feature
(if working on the file at home), could be a problem when it's taken back to
work.

Aladin Akyurek wrote:

Dave Peterson wrote:
[...]
(And I don't use the List feature much, either.)

Bryan Hessey wrote:

Hi Dave, and thanks for the 'List' - obviously a feature that I don't
use.
[...]

In fact, it's a significant "addition." Some of the important features
of the list functionality a

[1] It largely eliminates the need for dynamic named ranges that
adversely affect the performance. Whenever the list expands, the
formulas referring to ranges within the list adjust automatically. This
facilitates contructing pivot tables and graphs which include new data
immediately upon refresh.

=COUNTIF(B4:B13,2)

which refers to a range in A3:D13, converted into a list with
Data|List|Create List, automatically adjusted as:

=COUNTIF(B4:B13,2)

after a new entry in A14.

[2] Copying down the formulas is fully automatic within the list itself.
One shortcoming in this respect is that the copying is not reliable when
a formula refers to a (changing) range above the formula cell:

Assuming that the following formula in B3

=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),SUMPRODUCT( (A3$A$3:$A$16)+0)+1,"")

is copied down to B16 in a list in the range A2:D16.

Upon a new entry in A17, we get in B17:

=IF((A17<"")*ISNA(MATCH(A17,$A$2:A18,0)),SUMPRODU CT((A17$A$3:$A$18)+0)+1,"")

which is obviously wrong.

This forces us to have in B3 something like:

=IF((A3<"")*ISNA(MATCH(A3,$A$2:OFFSET(A3,-1,0),0)),SUMPRODUCT((A3$A$3:$A$16)+0)+1,"")

[3] Provides AutoFilter, a Total Row whose results can be referred to in
audit formulas.

--

Dave Peterson