validation zero

[Guest]

Hello. I have this small problem concerning the validation of the values the
users can enter in a table: the source list contains several cells than
aren't usually filled wiith values, therefore the validation treats them as
0. They come as a formula with reference to another sheet, so that's why I
believe the option "Skip blanks" does not work. The problem is some
operations in my table involve the COUNTA formula, and everytime a user
mistakenly enters 0 (from the drop down list created by the validation), this
0 value is counted and messes up my things there. How do I get rid of these 0
values?

Thanks,
Devlin

 

[Paul Morgan]

Hi Devlin,

You may have to sort the list , this would place zeros at the bottom,

or maybe

I have never tried this before , if you create another list with a formula such as

=if(a1=0,"",a1)
then use that list for the dropdown menu, will data validation then ignore the blanks??

here is other information on data validation

http://www.contextures.com/xlDataVal01.html
http://www.datapigtechnologies.com/flashfiles/datavalidation.html

 

[Anne Troy]

=countif(range,>0)
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[Bob Phillips]

Typo Anne.

=COUNTIF(rng,">0")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)