dynamic named range

[ExcelMonkey]

I have a dyanimce names range that starts in Cell A1. The formul
behined the dyanimc range is:

=Offset(A1,0,0,Counta(A1;A65000),1)

This works fine if I have blank values. But what if the blank value
are generated by formulas. That is, I use "" in a formula to creat
the impression that it is blank when in reality it has a formula in i
which generates a blank result.

Is it possible to create a dynaimc range which allows you t
incorporate blank cells?

I am assuming that I have to replace the Counta(A1;A65000) with som
sort of SUMIF(A1;A65000, "").

Thanks in advanc

 

[Frank Kabel]

Hi
try:
=Offset(A1,0,0,SUMPRODUCT(MAX((A1:A65000<>"")*ROW(A1:A65000))),1)

 

[Alan Beban]

I have a dyanimce names range that starts in Cell A1. The formula
behined the dyanimc range is:

=Offset(A1,0,0,Counta(A1;A65000),1)

This works fine if I have blank values. But what if the blank values
are generated by formulas. That is, I use "" in a formula to create
the impression that it is blank when in reality it has a formula in it
which generates a blank result.

Is it possible to create a dynaimc range which allows you to
incorporate blank cells?

I am assuming that I have to replace the Counta(A1;A65000) with some
sort of SUMIF(A1;A65000, "").

Thanks in advance

What do you mean "incorporate blank cells"?

I.e., suppose you have A1:A5 containing 1,2,ok,hello,5; A6:A9 containing
="" (the empty string); and A10:A65000 blank (i.e., containing empty
blanks); what result do you want for the height of the range? If you
want 5, then you might consider

=OFFSET(A1,0,0,(COUNTA(A1:A65000)-COUNTIF(A1:A65000,"")+COUNTIF(A1:A65000,"=")),1)

COUNTA should return 9 (i.e. the number with data plus the number of
empty strings); the first COUNTIF should return the total of empty
strings and empty blanks (within the Used Range portion of A1:A65000);
and the second COUNTIF should return the total of empty blanks (within
the Used Range portion of A1:A65000).

Alan Beban