1

The scenario I have written supposed to work like below...

A has 4 multiple choice options, (Completed, Suspended, Blocked, Cancelled)

B has 4 multiple choice options (Completed, Suspended, Blocked, Cancelled)

The same for C and D.

If A is completed then only B can be chosen (any value) and if the user selects B as completed, then C can be selected (any value) and other columns will not be able to be chosen. I have spent many scenarios but none of them seem to be work, some function such as only if all are completed, you can submit it. I would appreciate, if someone corrects my expression I have written below. Thanks beforehand.

=IF(

AND(          

OR(   

(AND([B]<>"";[A]="completed")); 

(OR(  (AND([B]="completed";[C]<>""));  (AND([B]<>"";[C]<>"")); )  ); );   

);  TRUE;FALSE) 
0

You could try the below validation formula:

=AND(OR(ISBLANK([B]),[A]="Completed"),OR(ISBLANK([C]),[B]="Completed"),OR(ISBLANK([D]),[C]="Completed"))

Test:

enter image description here

  • This formula would be really helpful to save characters before reaching 1024 character limit which I'm facing right now with the first formula suggestion provided. However, using your formula I can change the state of [D] to be completed by leaving the rest of the columns ISBLANK because of OR. However in my case, [D] can only be completed if [A],[B],[C] is completed. Or [C] can only be completed if [A], [B] completed. I would appreciate any recommendation in order to achieve this. If [A] completed [B] can be in any state, but once [B] completed we can only move to the next state,[C] – Abbas Oct 10 at 11:55
  • Hi. Per my test, it works fined on my end. I cannot change the state of D to be completed by leaving the rest of the columns ISBLANK. I would add the test results in my answer. – Michael Han_MSFT Oct 11 at 1:34
  • Hi Michael, my excuses. Previously I put your statement into =IF( ;TRUE;FALSE) formula and got different functionality but trying with just =AND solved the situation. My mistake. Many thanks for the support :) – Abbas Oct 11 at 13:21
  • If my answer is helpful to you, please accept it. – Michael Han_MSFT Oct 11 at 15:10
0

As per my understanding, what you want is a combination of 4 cascading drop downs.

The drop down must contain a value only if the preceding drop down value is "Completed".

Based on this logic you can try following formula. You may also try using ISBLANK function or modify according to your logic.

=IF(
    OR(
        AND(
            AND([A] <> ""; [B] == "");
            AND([C] == ""; [D] == "")
        );
        OR(
            AND(
                AND([A] == "Completed"; [B] <> "");
                AND([C] == ""; [D] == "")  
            );
            OR(
                AND(
                    AND([A] == "Completed"; [B] == "Completed");
                    AND([C] <> ""; [D] == "")
                );
                OR(
                    AND(
                        AND([A] == "Completed"; [B] == "Completed");
                        AND([C] == "Completed"; [D] <> "")
                    );
                    AND(
                        AND([A] == "Completed"; [B] == "Completed");
                        AND([C] == "Completed"; [D] == "Completed")
                    )
                )
            )
        )
    );
    TRUE;
    FALSE
)
  • Definitely what I was looking for. Works on 1024 characters but unfortunately, the character limit is out of the boundary in my case (more than 4000 characters). Abbreviations and shortening made it around 3000 characters. Are any methods available to overcome these boundary level? Such as reading from text file or executing the formula in Excel and using the compiled result inside the formula? I could not find much information, unfortunately. By the way, very appreciate the effort you put on this question. – Abbas Oct 9 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.