# Help about Validation number into a cell

#### Maurizio

##### Well-known Member
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

### 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
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
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

##### Well-known Member
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
=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
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)
I need validation into cell "F2" without any other support cell.

##### Well-known Member
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)

#### Maurizio

##### Well-known Member
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)
ok