Question about Data Validation.

O

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.
 
Sort by date Sort by votes

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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))
 
Upvote 0
omairhe said:
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?
Click to expand...

try post#12 below

yes 8 can be dynamic as count(A3:A8)
 
Upvote 0
AlanY said:
try post#12 below

yes 8 can be dynamic as count(A3:A8)
Click to expand...
same result. unable to get a TRUE no matter what I type in. Tried both Ctrl+Shift+Enter eg. {} and without the curly brackets.
 
Upvote 0
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))
 
Upvote 0
AlanY said:
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))
Click to expand...


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)
 
Upvote 0
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)))
 
Upvote 0
AlanY said:
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)))
Click to expand...

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
Click to expand...

For instance when A2 = B
 
Upvote 0
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.
 
Upvote 0
omairhe said:
Yes, very very close. Only two requirements remain now. Please don't give up now.. hehe



For instance when A2 = B
Click to expand...
I’m away from my laptop now, will have another go when I’m back
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
210
Jeffrey Mahoney
Jeffrey Mahoney
E
  • Question
Data Validation Source for dropdown cell based on variable range?
Replies
2
Views
79
EssKayKay
E
B
  • Solved
Data Validation from a list OR Sunday date?
2
Replies
12
Views
561
Peter_SSs
Peter_SSs
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
403
AndyPandy27
A
R
  • Solved
Data Validation
2
Replies
19
Views
1K
dreid1011
dreid1011

Forum statistics

Threads
1,214,659
Messages
6,120,781
Members
448,992
Latest member
prabhuk279

Share this page

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