You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Kara" wrote in message
...
Brilliant! I can't thank you enough, you have no idea how many hours I
agonized over how to make this work, thanks so much Biff, I really
appreciate
it :)

"T. Valko" wrote:

Use this as your data validation formula:

=AND(I11=MIN(IF(A3:A8=D11,B3:B8)),I11<=MAX(IF(A3: A8=D11,C3:C8)))

--
Biff
Microsoft Excel MVP


"Kara" wrote in message
...
I have a small table in excel, which appears as follows:
A B C
EX-1 94000 110700
EX-2 105400 124100
EX-3 118000 138900
EX-4 135500 159500
EX-5 151800 178700
MOF-5 117206 144551

Below this chart, I will require many data entries. Column D is a drop
down
list which forces users to pick one of the values in column A above.
Column
I is where users will have to enter a valid number--in this case, I am
trying
to create a data validation formula in column I which will allow users
to
enter any value beween the minimum value in B and the maximum value in
C,
based on the value they chose in column D.

I have read a lot of info on the nested IF function, and understand
that
you
can nest up to 7 layers. However, in the data validation formula, as
soon
as
I enter more than 4 layers, nothing works. Stop at only 4, and my
validation
works perfectly.

Here is the formula I want to enter:
=IF(AND(D11=$A$3,I11=$B$3,I11<=$C$3),I11,IF(AND(D 11=$A$4,I11=$B$4,I11<=$C$4),I11,IF(AND(D11=$A$5,I 11=$B$5,I11<=$C$5),I11,IF(AND(D11=$A$6,I11=$B$6, I11<=$C$6),I11,IF(AND(D11=$A$7,I11=$B$7,I11<=$C$7 ),I11,IF(AND(D11=$A$8,I11=$B$8,I11<=$C$8),I11)))) ))

However, the only way it seems to work is if I remove 2 of the
arguments:
=IF(AND(D11=$A$3,I11=$B$3,I11<=$C$3),I11,IF(AND(D 11=$A$4,I11=$B$4,I11<=$C$4),I11,IF(AND(D11=$A$5,I 11=$B$5,I11<=$C$5),I11,IF(AND(D11=$A$6,I11=$B$6, I11<=$C$6),I11))))

Can somebody please help me? I'm not completely new to excel, but I
cannot
for the life of me figure out why this doesn't work--why it stops at 4,
if
I
enter a fifth "if", none of the data validation works.

Thanks, Kara