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.
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.
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.
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.
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.
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
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?