dynamic named range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelMonkey - 06 Oct 2004 10:55 GMT

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

Reply to this Message

Frank Kabel - 06 Oct 2004 11:29 GMT

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

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> I have a dyanimce names range that starts in Cell A1. The formula
> behined the dyanimc range is:
[quoted text clipped - 4 lines]
> 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

> which generates a blank result.
>
[quoted text clipped - 5 lines]
>
> Thanks in advance

Reply to this Message

Alan Beban - 06 Oct 2004 12:33 GMT

> I have a dyanimce names range that starts in Cell A1. The formula
> behined the dyanimc range is:
[quoted text clipped - 13 lines]
>
> 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

Reply to this Message