Hi all,
After receiving unfavourable answers to my enquiry regarding truly blank
cells, I'll get a bit long-winded, and explain what I'm trying to do.
I have a list of text which is created by a formula which has been
filled-down about 100 rows. Currently, only the first 10 cells have text. All
the other cells appear blank, but are really ="" (inserted by an IF statement
in the formula).
As I add data to another part of the sheet, more cells in this list will
become visible text.
I wanted to make a dynamic named range from this list for use in a DV
dropdown.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
But the COUNTA counts all the ="" cells as well as the text, and they appear
in the dropdown as lots of blank rows. Despite the fact that they all appear
at the end of the dropdown, it's still annoying.
I was wondering if I could use COUNTIF instead of COUNTA, to only count text
string lengths of greater than zero, and would that work? I have tried, but
failed thus far.
Any help would be appreciated.
Dave.

=COUNTIF(C1:C10,""&0)
This will ignore zero's and "",
Regards,
Alan.
"Dave" wrote in message
...
Hi all,
After receiving unfavourable answers to my enquiry regarding truly blank
cells, I'll get a bit long-winded, and explain what I'm trying to do.
I have a list of text which is created by a formula which has been
filled-down about 100 rows. Currently, only the first 10 cells have text.
All
the other cells appear blank, but are really ="" (inserted by an IF
statement
in the formula).
As I add data to another part of the sheet, more cells in this list will
become visible text.
I wanted to make a dynamic named range from this list for use in a DV
dropdown.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
But the COUNTA counts all the ="" cells as well as the text, and they
appear
in the dropdown as lots of blank rows. Despite the fact that they all
appear
at the end of the dropdown, it's still annoying.
I was wondering if I could use COUNTIF instead of COUNTA, to only count
text
string lengths of greater than zero, and would that work? I have tried,
but
failed thus far.
Any help would be appreciated.
Dave.

I have a list of text
only the first 10 cells have text.
All the other cells appear blank, but are really =""

Assuming all blank cells will be at the bottom of the range:

=OFFSET(A1,,,COUNTIF(A1:A100,"?*"))


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi all,
After receiving unfavourable answers to my enquiry regarding truly blank
cells, I'll get a bit long-winded, and explain what I'm trying to do.
I have a list of text which is created by a formula which has been
filled-down about 100 rows. Currently, only the first 10 cells have text.
All
the other cells appear blank, but are really ="" (inserted by an IF
statement
in the formula).
As I add data to another part of the sheet, more cells in this list will
become visible text.
I wanted to make a dynamic named range from this list for use in a DV
dropdown.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
But the COUNTA counts all the ="" cells as well as the text, and they
appear
in the dropdown as lots of blank rows. Despite the fact that they all
appear
at the end of the dropdown, it's still annoying.
I was wondering if I could use COUNTIF instead of COUNTA, to only count
text
string lengths of greater than zero, and would that work? I have tried,
but
failed thus far.
Any help would be appreciated.
Dave.

Hi,
Thanks heaps. The "?*" condition worked perfectly.
Pooh to needing truly blank cells!
By the way, the ""&0 condition gave a count of 0 (zero). Don't know why.
Thanks again.
Dave.

You're welcome!

the ""&0 condition gave a count of 0 (zero). Don't know why.

It was using the condition of 0 (greater than 0). So, it would only count
numbers greater than 0.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi,
Thanks heaps. The "?*" condition worked perfectly.
Pooh to needing truly blank cells!
By the way, the ""&0 condition gave a count of 0 (zero). Don't know why.
Thanks again.
Dave.

Hi Biff,
Thanks for that. XL sure is picky about what type of data is in a cell. I
guess it makes it more precise though.
Dave.