data validation question

[helpplease!]

Hi,

A B ...
X M
Y
Z
0
0
0
...

here is my question:in cell B1 (M above), i need to do a data validation list formula of col A, but only those that have a value AND not equal to 0. Now each cell in col A contains a formula that may return a string(eg X Y Z),"" or 0.

i can easily get rid of the 0's by making them " ", but the blanks still get included in the list.

i tried this formula in Data validation > Allow List > source field:

=OFFSET(sheet1!$A$1,0,0,COUNTA(sheet1!$A$1:$A$100),1)

any help much appreciated...

helpplease!

[NBVC]

Assuming only text strings are valid....try:

=A1:INDEX(A:A,MATCH(REPT("Z",255),A:A))

[helpplease!]

that worked thanks!

[helpplease!]

hmmm is "" considered a text string? because it is now included which i don't want...

i have changed all 0's to "" so as not to be displayed, but the blanks are now showing in the data validation selection.

any ideas?

thanks,

helpplease!

[NBVC]

Try instead:

=A1:INDEX(A1:A65534,MATCH(2,1/(A1:A65534<>"")))

this will not eliminate blanks within the range though...it only goes to the last text (no blank) string.

[helpplease!]

genius! a prerequisite is that there are no blanks in between the range so that is fine...