formula custom data validation
If you are using XL2003 or earlier, there is a limit of 7 levels of
nesting with functions - not just with IFs. You are using IF functions
and AND functions, and so you reach this limit after 4 IFs.
Perhaps you need to revise your data validation rules.
Pete
On Feb 6, 11:57*am, Kara wrote:
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, I11=$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, I11=$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
|