Dynamic range: Has something changed in Excel 2007?

bydesign said:

All

In Excel 97 & 2003, I have been using dynamic ranges to count the total number of cells with data in a column using =OFFSET(SF!$G$2,0,0,COUNTA(SF!$G:$G)-1). This has always worked well.

In Excel 2007, using the same formula; I am getting a return of the value in G9... not the total number of cells with data in column G.

Column G has a title row and 137 contiguous cells with data.

When I edit the formula by opening the "Function Arguments" window (e.g. click "fx") the proper value of 136 is displayed next to the "height" argument.


Any ideas?

Click to expand...

I think, ms in both the versions work the same
You may have smt wrong

try to:

I think TigerTiger is correct. What you see for "height" is a part of your formula but not the final result:
COUNTA(SF!$G:$G)-1 '//Count of Cells in Column G - 1

You formula will return a dynamic range, but you need to do something with it (e.g. SUM, MAX etc.). Simply refering to it will not result in any agregate result.

Try placing the formula in a named range:

Insert > Name > Define (not sure how this translates in xl2007):
Refers to: MyRange
Formula: =OFFSET(SF!$G$2,0,0,COUNTA(SF!$G:$G)-1)

Now click back on the name MyRange, then click into the formula - it should place an outline around the array that it returns.

An alternative to the OFFSET method could be (and an alternative that I prefer):

=SF!$G$2:INDEX(SF!$G:$G,MATCH(9.99999999999999E+307,SF!$G:$G))
--- assume that column G houses numbers

=SF!$G$2:INDEX(SF!$G:$G,MATCH(REPT("z",255),SF!$G:$G))
--- assume that column G houses text

OFFSET() is a volatile worksheet function and better avoided where there is an alternative method available.

Thanks Jon - I did not realize Offset was volatile - and its a favorite {sigh}.

The question I've been longing to ask: how do you remember how many nines to type in ...

Thanks for the responses...

I should have noted that I am using =OFFSET(SF!$G$2,0,0,COUNTA(SF!$G:$G)-1) in a named range called "DataRange".

The result in the Name Manager for value is {...}

I originally learned this technique from John Walker http://spreadsheetpage.com/index.php/tip/update_charts_automatically_when_you_enter_new_data/

I have compared to this again and have matched his work...
I'll have to dig some more.

Alexander Barnes said:

Thanks Jon - I did not realize Offset was volatile - and its a favorite {sigh}.

The question I've been longing to ask: how do you remember how many nines to type in ...

Click to expand...

Aaah I don't have to! I have it named as BigNum in my default workbook Book.XLT. That way it's readily available whenever I need it.

What are you trying to use the dynamic range for? Again I think the formula is ok, perhaps the problem lies in the application of it.

Thanks Jon! I'm going to add this to my book.xlt too.

I'm a bit fuzzy in this thread but looking at the ozgrid page and the posts again, its sounds like we are talking about a formula entered as a defined name, not a function. Probably you need to go to Name Manager per Jon's suggestion and enter it there (giving it a name "DataRange") but it shouldn't be returning a value (?) - in essence it's a range of cells, not a count or a sum. That's why you can enter it as a chart series reference - or count/sum as you like -- =Count(DataRange) --