Advanced Dynamic Ranges

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

The standard

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

=OFFSET($H$1,0,0,COUNT($H:$H),1)

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:

=OFFSET($H$1,0,0,MATCH(1E+306,$H:$H,1),1)

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

=OFFSET($H$1,0,0,MATCH(“*”,$H:$H,-1),1)

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:

=OFFSET(Sheet1!$A$1,0,0,1+TODAY()-DATE(YEAR(TODAY()),1,1),1)


Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  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))