Hi confused,

how do I refer to a column when the number of rows may change
dynamically? e.g column A with 12 rows of data would be a1:A12, with
dynamic number of rows it would be A1:A??


I know that COUNTA can be used to return the number of non empty rows

good! combine Counta() with the OFFSET()-function. eg.

=offset(a1,0,0,counta(whatever), numberofcolums)

you can use this formula to specify the data range of named ranges eg.
"myrange", to sum up everything you could use then a formula like
=sum(myrange)

read online help to the offset-function.

arno