Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula custom data validation
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula custom data validation
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula custom data validation
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula custom data validation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation custom formula | Excel Worksheet Functions | |||
Custom data validation | Excel Discussion (Misc queries) | |||
Data Validation - Scroll in the formula bar for a custom criteria | Excel Worksheet Functions | |||
Data Validation - Custom - Formula | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |