Help about Validation number into a cell

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
669
Office Version
2007
Platform
Windows
Hi all,

how can I input into a cell a number with this limits:

1° number value from 1 to 9
2° number value from 2 to 10
but 2° number > 1° number (or 1° number < 2° number)

i.e. ok:
12 (ossia 1 and 2)
39 (ossia 3 and 9)
710 (ossia 7 and 10)
910 (ossia 9 and 10)

KO:
98 (ossia 9 and 8)
99 (ossia 9 and 9)
109 (ossia 10 and 9)

Tia. Maurizio
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,348
Office Version
365
Platform
Windows
One way

Use data validation in cell B2 as follows
Allow:Custom
Formula
=AND(VALUE(LEFT(B2,1))>0,VALUE(MID(B2,2,LEN(B2-1)))>1,VALUE(MID(B2,2,LEN(B2-1)))<11,OR(VALUE(LEFT(B2,1))<VALUE(MID(B2,2,LEN(B2-1))),VALUE(MID(B2,2,LEN(B2-1)))<VALUE(LEFT(B2,1))))
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,348
Office Version
365
Platform
Windows
I have jst spotted that the above is has not tested everything. I will return today (out of time right now) :)
In the meantime, perhaps you can solve this yourself
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
818
Office Version
2016
Platform
Windows
Hi Maurizio,

Does this do what you want?

Book2
AB
1Number 1Number 2
2910
Sheet1
Cells with Data Validation
CellAllowCriteria
A2Whole numberbetween 1 and 9
B2Custom=AND(B2>=2,B2<=10,B2>A2)
 

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
669
Office Version
2007
Platform
Windows
=AND(VALUE(LEFT(B2,1))>0,VALUE(MID(B2,2,LEN(B2-1)))>1,VALUE(MID(B2,2,LEN(B2-1)))<11,OR(VALUE(LEFT(B2,1))<VALUE(MID(B2,2,LEN(B2-1))),VALUE(MID(B2,2,LEN(B2-1)))<VALUE(LEFT(B2,1))))
Sorry but it doesn't work fine!
 

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
669
Office Version
2007
Platform
Windows
Hi Maurizio,

Does this do what you want?

Book2
AB
1Number 1Number 2
2910
Sheet1
Cells with Data Validation
CellAllowCriteria
A2Whole numberbetween 1 and 9
B2Custom=AND(B2>=2,B2<=10,B2>A2)
Hi Toadstool,
I need validation into cell "F2" without any other support cell.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
818
Office Version
2016
Platform
Windows
Hi Toadstool,
I need validation into cell "F2" without any other support cell.
This?

Maurizio.xlsx
ABCDEF
1Number 1Number 2
298
Sheet1
Cells with Data Validation
CellAllowCriteria
F2Custom=AND(A2>=1,A2<=9,B2>=2,B2<=10,B2>A2)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,353
Messages
5,486,367
Members
407,542
Latest member
Tyronaught

This Week's Hot Topics

Top