Question about Data Validation.

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
last try

Book1
A
1TRUE
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))
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
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?
try post#12 below

yes 8 can be dynamic as count(A3:A8)
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,834
Office Version
2019
Platform
Windows
try post#12 below

yes 8 can be dynamic as count(A3:A8)
same result. unable to get a TRUE no matter what I type in. Tried both Ctrl+Shift+Enter eg. {} and without the curly brackets.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
have a look of this test
try to copy below to see if you can produce same results

Book1
A
1TRUE
2B
31
41
51
6-1
7-1
8-1
91
10-1
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))


Book1
A
1FALSE
2A
31
41
51.1
6-1
7-1
8-1
91
10-1
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))


Book1
A
1TRUE
2A
31
41
55
6-1
7-1
8-1
91
10-1
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))


Book1
A
1FALSE
2
31
41
55
6-1
7-1
8-1
91
10-1
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,834
Office Version
2019
Platform
Windows
have a look of this test
try to copy below to see if you can produce same results

Book1
A
1TRUE
2B
31
41
51
6-1
7-1
8-1
91
10-1
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))


Book1
A
1FALSE
2A
31
41
51.1
6-1
7-1
8-1
91
10-1
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))


Book1
A
1TRUE
2A
31
41
55
6-1
7-1
8-1
91
10-1
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))


Book1
A
1FALSE
2
31
41
55
6-1
7-1
8-1
91
10-1
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))

I was testing your formulas on empty range. for instance A3:A10 was empty . so I put A3 = 5 and A2 = A , it was FALSE.
Now that I have all the cells in range A3:A10 filled up with data. I am getting the same result as you are having.
How do I allow empty cells in range A3:A10 without affecting the formula result?
Thanks

Edit: if all cells in range are empty this should give TRUE. because at least A2 is not empty or not an A. If A2 was to remain empty then this should give FALSE.
Hence the first condition is for A2 to have either an A or anything other than A. (B, C or D etc)
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
this may work

Book1
A
1TRUE
2B
3
4
5
6
7
8-1
91
10-1
Sheet1
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))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNT(A3:A10)))
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,834
Office Version
2019
Platform
Windows
this may work

Book1
A
1TRUE
2B
3
4
5
6
7
8-1
91
10-1
Sheet1
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))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNT(A3:A10)))
Yes, very very close. Only two requirements remain now. Please don't give up now.. hehe

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
For instance when A2 = B
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,834
Office Version
2019
Platform
Windows
only A should allow negative integers along with positive. if any.
however, any other value other than A in A2 should only allow positive numbers.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
Yes, very very close. Only two requirements remain now. Please don't give up now.. hehe



For instance when A2 = B
I’m away from my laptop now, will have another go when I’m back
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,863
Messages
5,507,782
Members
408,647
Latest member
gazmoz17

This Week's Hot Topics

Top