0

I'm looking to create a formula that returns different values depending on whether or not two boxes are checked, but only when a certain picklist value is selected. Meaning if a different picklist value is used, the returned value will not be affected by whether or not the boxes are checked. So:

If picklist value is A, neither box checked = A
"   "          "   " A, one box checked = A + B
"   "          "   " A, both boxes checked = A + 2B

Any suggestions are much appreciated.

4
  • Did you already try writing anything ? Sep 4 '14 at 15:37
  • 1
    I can give you a formula that would give you the output described in your question, but I don't think that is going to help you much. What have you tried so far and are you able to provide more detail on your inputs and what the output should look like? Sep 4 '14 at 15:38
  • What if picklist value is not A? Just want a null value of the picklist is anything other than A? Sep 4 '14 at 15:38
  • The formula is quite complex, and it's currently returning the correct values for all other pick values. I've tried: IF(AND( ISPICKVAL(Picklist, A),AND(Box1,Box2)),Value A, IF(OR(Box1,Box2)),Value B, Value C)
    – Ian
    Sep 4 '14 at 16:01
2

The way that you have it, it looks to see IF pickval = A AND both boxes are checked, it returns value A. If that is not the case then it just looks that box1 OR box2 are checked, but has no check at all on the pick-list value, so even if pick-list value is 'X', and one of the boxes are checked, then you get value B.

Your Formula

IF(
    AND(
        ISPICKVAL(Picklist, A),
        AND(
            Box1,
            Box2
        )
    ),
    Value A, 
    IF(
        OR(
            Box1,
            Box2
        ),
        Value B, 
        Value C
    )
)

From your description, I don't think this is what you want. You want to check for Value 'A' as the pick-list value in both scenarios. To do that you would need to adjust it to this.

IF(
    ISPICKVAL(Picklist, 'A'),
    IF(
        AND(
            Box1,
            Box2
        ),
        'Value A',
        IF(
            OR(
                Box1,
                Box2
            ),
            'Value B',
            'Value C'
        )
    ),
    'Value you want if Picklist os NOT A'
)

Again, this is making some assumptions on your use case, so correct me if I am mis interpreting anything.

2
  • This looks great. To clarify, I could replace 'Value you want if Picklist is NOT A' with further IF statements for other picklist values e.g. IF(ISPICKVAL(Picklist, B),Value D, IF(ISPICKVAL(........?
    – Ian
    Sep 4 '14 at 16:52
  • Yes, exactly. If you want to further check for other Picklist values, that is how you would do it. Sep 4 '14 at 17:04

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.