Question about Data Validation.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,835
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Mar 26, 2009
Messages
1,835
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,161
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

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

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,835
Office Version
  1. 2019
Platform
  1. Windows
=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
Joined
Oct 30, 2014
Messages
4,161
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

ok, try

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

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,835
Office Version
  1. 2019
Platform
  1. Windows
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..
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,161
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

back later
 
Last edited:

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,835
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,161
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Mar 26, 2009
Messages
1,835
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,855
Messages
5,525,227
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top