Dynamic Named Ranges

I use named ranges a lot. The only downside is that many times I need those ranges to grow as my spreadsheet grows. For that, I use dynamic named ranges employing the OFFSET() and COUNTA() functions.

To create a dynamic named range, select Insert > Name > Define from the menu.

DynamicRng1

The formulas starts in A2, moves down and to the right zero, and resizes to a variable number of rows long and one column wide. The variable number of rows is defined by COUNTA()-1 (the minus one is for the column header). This works if you have no gaps in your data. If there’s a blank cell in there, you won’t get the correct results. Here’s what it looks like in action.

DynamicRng2

DynamicRng3

5 Comments

  1. Toad says:

    Dick, I started using dynamic ranges in my models last year, because I was told it would reduce recalculation times. Before that, I used to just define ranges using whole columns or rows so that new data would always be included. I’m wondering if there comes a point when calculating the dynamic range takes just as long as calculating those big ranges I used to use.

    I hope that question made some sense. If it didn’t, please just ignore it and go have a beer or something.

  2. Andrew says:

    Toad, using entire columns or rows does use an awful lot of memory. I don’t know which takes longer but I’d say that calculating dynamic ranges shouldn’t take as long, (depending on the amount of data to be calculated).

    If what I’ve said is totally wrong, I’ll have a beer and drink to forget.

  3. Jamie Collins says:

    Charles Williams’s site has a lot of info on optimizing calculation speeds, including how to speed up dynamic ranges

    http://www.decisionmodels.com/optspeedf.htm

    e.g. put the COUNTA in a cell formula on the same sheet and reference the cell.

    Although dangerously close to an advertisement on his behalf (which I can’t actually do because I don’t own any of his products!), I recommend that anyone interested in dynamic ranges download the following demonstration of his optimization techniques:

    http://www.decisionmodels.com/Downloads/FxlSample.zip

    Jamie.

    –

  4. Felipe Camargo says:

    Hi,

    I’d like to know if it’s possible to use another function inside de OFFSET function to specify the reference cell. I’m trying to use an index/Match but it doesn’t seem to be working, am I doing something wrong here?

    thanks

    Felipe

  5. Andy Miller says:

    I use this method quite frequently now. However, I just discovered something. I can’t use the INDIRECT function with named ranges that are defined this way. Is there a workaround for that?

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: