Solved: Cell Value Validation

[lacviet2005]

Hi all gurus,
I need your help with my formula for validation:
=OR(IF(E13=1,N12>=13,N12<=20),IF(E13=2,N12>=20,N12<=80))

I have 2 options in cell E13, either 1 or 2.
If E13=1, then the value that the user can input in cell (N12) is between 13 to 20.
OR
If E13=2, then the value must be between 21 to 80.

Please look at the formula above, couldn't get it to work. I have try OR AND statements but no result.

Many thanks,
LV

[PhilJette]

How about this:

Create 2 named ranges somewhere in the worksheet, one from 13 - 20 (call this one "low") and one from 21 - 80 (call this one "high").
Then select Cell N12 and go to Data > Validation. In the validation Criteria select Allow: List, and use this formula for the source:
=IF($E$13=1,low,high)

in order to ensure users enter only 1 or 2 in cell E13, you can use validation onthis cell as well.

[lacviet2005]

Thanks Phil. But your method is limited to enter decimal number in cell N12.

There must be a better way to do this. Any ideas Phil? Anybody?

Thanks in advanced.

[PhilJette]

No problem. As you can see I'm bored at work today! I'm not too sure what you mean regarding being limited to decimal numbers though...
Another option along the same lines would be to have one hidden cell find the minimum:
[VBA]=IF(E13=1,13,21)[/VBA]
And one find the maximum:
[VBA]=IF(D12=1,20,80)[/VBA]

Then once again select N12 and go to validation, but this time select
Allow: Whole Number
Data: Between
then for the minimum select the cell containing your minimum formula, and for the maximum select the cell containing the maximum formula.

Once again validation could be used on E13 to allow only 1 or 2 to be enetered.

[lacviet2005]

Woohoo....i got it.
I just tweaked my formulae a little bit and it worked!

[Bob Phillips]

Use Data Validation in N12 with an Allow type of Custom, and a formula of

=OR(AND(E13=1,N12>=13,N12<=20),AND(E13=2,N12>=20,N12<=80))

[lacviet2005]

No IF function? Didn't think of that.

Thanks XLD. I was hoping for your response. I know that i could count on you. But i got it.

Cheers!
LV

[Bob Phillips]

No IF is required because all you want is TRUE or FALSE for DV to act upon. AND/OR does that nicely.