Question about Data Validation.

[omairhe]

Hello all,

I find data validation to be a useful tool in excel 2019.
In cell A2 if the value = A then allow both positive and negative integers, If not an A then allow only positive integers, and if blank then do not allow anything. How to achieve this via Data Validation?

Please note that exact zero "0" value should not be allowed in any condition.

Many thanks and appreciations.

 

[omairhe]

oki but please check post 19 as we both kind of posted at the same time.. appreciate your efforts a lot.

 

[omairhe]

=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))=COUNT(A3:A10)))

Yes. I think I got this. Had to alter from this <>0 into this >0
I thank you for this AlanY.
t c.

 

[AlanY]

try

Book1
	A
1	TRUE
Sheet1

Cell Formulas
Range	Formula
A1	A1	=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNTIF(A3:A10,">0")))

 

[omairhe]

try

Book1
	A
1	TRUE
Sheet1

Cell Formulas
Range	Formula
A1	A1	=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNTIF(A3:A10,">0")))

Dear Alan,

In your post #17 you had come closest to achieving my requirements and by removing this "<" bracket I was able to get all of my requirements in post #2 fulfilled. For example,

Your Formula from post #17

=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNT(A3:A10)))

After removing the underlined bracket from the end of the formula

=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))=COUNT(A3:A10)))

Sorry to have repeat this as I've already mentioned this in my last post to you..
Thanks again...

 

[AlanY]

still not quite right though
this gives correct answer

Book1
	A
1	FALSE
2	C
3
4	1
5
6	-1
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
A1	A1	=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNTIF(A3:A10,">0")))

but this isn't

Book1
	A
1	TRUE
2	C
3
4	1
5
6	-1
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
A1	A1	=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))=COUNTIF(A3:A10,">0")))