Advanced Dynamic Ranges

October 4th, 2008 | Categories: Formulas | Tags: , , , ,

The standard

dynamic range uses a named range based on the COUNTA formulae:


However, this only works where there are no blanks in the data. If the data is purely numeric, and contains blank cells in the data then use a range such as:


If the data is purely text then we will need a named range such as:


And if we have a mixture of text, numbers and spaces:

=OFFSET(Sheet1!$H$1,0,0,MAX(MATCH(1E+306,Sheet1!$A:$A,1), MATCH(“*”,Sheet1!$H:$H,-1)),1)

Where we take the maximum of the two previous match functions.
If we need a range that expands for every day of this calendar year:


  1. sam
    October 12th, 2008 at 06:17
    Reply | Quote | #1

    Offset is volatile and hence use Index in a dynamic name

    = Index($A:$A,Counta($A:$A))