#### omairhe

##### Well-known Member
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.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### omairhe

##### Well-known Member
on second thought, could this be achieved via formula instead of Data Validation. Trick is that I can then put the formula in Cell A1 and insert in Data Validation custom formula =\$A\$1=TRUE
this will not allow the user to type in cell range A3:A10 anything if the value is other than TRUE in A1.

I checked this by typing in cell A1 = FALSE / TRUE and data validation worked even with a dummy formula.

So what I would be needing in A1 formula is
IF A2 = A and range A3:A10 consists of negative integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of both positive and negative integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of negative integer/s then A1 = FALSE
IF A2 = not blank and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of both positive and negative integer/s then A1 = FALSE
IF A2 = A or not blank and range A3:A10 consists of exact zero "0" value then A1 = FALSE
IF A2 = blank then A1 = FALSE

Anyone would like to give it a try please?

#### AlanY

##### Well-known Member
try

=OR(A2="A",AND(NOT(ISBLANK(A2)),COUNTIF(A3:A10,"="&8)=0))

#### omairhe

##### Well-known Member
=OR(A2="A",AND(NOT(ISBLANK(A2)),COUNTIF(A3:A10,"="&8)=0))

only the following strike through is achieved from the formula above;

IF A2 = A and range A3:A10 consists of negative integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of both positive and negative integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of negative integer/s then A1 = FALSE
IF A2 = not blank and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of both positive and negative integer/s then A1 = FALSE
IF A2 = A or not blank and range A3:A10 consists of exact zero "0" value then A1 = FALSE
IF A2 = blank then A1 = FALSE

#### AlanY

##### Well-known Member

ok, try

=OR(AND(A2="A",COUNTIF(A3:A10,"="&0)=8),AND(NOT(ISBLANK(A2)),COUNTIF(A3:A10,"<>"&0)=8))

#### omairhe

##### Well-known Member
ok, try

=OR(AND(A2="A",COUNTIF(A3:A10,"="&0)=8),AND(NOT(ISBLANK(A2)),COUNTIF(A3:A10,"<>"&0)=8))

Now the following strike-throughs are achieved

IF A2 = A and range A3:A10 consists of negative integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of both positive and negative integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of negative integer/s then A1 = FALSE
IF A2 = not blank and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of both positive and negative integer/s then A1 = FALSE
IF A2 = A or not blank and range A3:A10 consists of exact zero "0" value then A1 = FALSE
IF A2 = blank then A1 = FALSE

Please note that integer is a whole number and thus should consist of 5 and not 5.3 for instance..

back later

Last edited:

#### omairhe

##### Well-known Member
one more try, note that it's a array formula,

Book1
A
1FALSE
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",(COUNT(1/(INT(IF(A3:A10>0,A3:A10))=IF(A3:A10>0,A3:A10)))+COUNT(1/(INT(IF(A3:A10<0,A3:A10))=A3:A10)))=8),AND(NOT(ISBLANK(A2)),COUNT(1/(INT(IF(A3:A10>0,A3:A10))=IF(A3:A10>0,A3:A10)))=8))
Press CTRL+SHIFT+ENTER to enter array formulas.

These are the results:

IF A2 = A and range A3:A10 consists of negative integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of both positive and negative integer/s then A1 = TRUE

IF A2 = not blank and range A3:A10 consists of negative integer/s then A1 = FALSE
IF A2 = not blank and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of both positive and negative integer/s then A1 = FALSE
IF A2 = A or not blank and range A3:A10 consists of exact zero "0" value then A1 = FALSE
IF A2 = blank then A1 = FALSE

not blank means the cell is not specifically consists letter A but the cell may contain B or C or any other letter.
You are getting closer..

#### AlanY

##### Well-known Member
if this still didn't work, I'm done

Book1
A
1FALSE
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))=8))

#### omairhe

##### Well-known Member
if this still didn't work, I'm done

Book1
A
1FALSE
Sheet2
Cell Formulas
RangeFormula
A1A1=OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))=8))

The formula is giving me a FALSE. I cannot make the formula TRUE no matter what I type in the range or in cell A2.
Please I would like you to make an effort for a formula that can adapt to various Range sizes. Currently the range is not big. For instance A3:A10, in other sheets the range is A3:A40 and many other sheets where range is even larger. All need to have your formula in them. I see an 8 at the end and I assume I will have to manually change it for other ranges? Correct?

Replies
1
Views
19
Replies
5
Views
63
Replies
2
Views
32
Replies
1
Views
59
Replies
3
Views
70