Question about Data Validation.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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?
 
Upvote 0
try

=OR(A2="A",AND(NOT(ISBLANK(A2)),COUNTIF(A3:A10,"="&8)=0))
 
Upvote 0
=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
 
Upvote 0
ok, try

=OR(AND(A2="A",COUNTIF(A3:A10,"="&0)=8),AND(NOT(ISBLANK(A2)),COUNTIF(A3:A10,"<>"&0)=8))
 
Upvote 0
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..
 
Upvote 0
back later
 
Last edited:
Upvote 0
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..
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top