Multiple IF AND ISBLANK Criteria in Formula

What I'm trying to accomplish:

  1. If [Approved] is checked, change [Status] to "Complete"
  2. If [Ad RSS Link], [Subject Line], and [Done] are not blank, change [Status] to "Proofing"
  3. If [Ads Entered] is checked, change [Status] to "Proofing"
  4. If none of the above are true, [Status] will be "Waiting for Information"

Here is the formula I'm using, but it keeps coming back as UNPARSEABLE. I've looked it over and retyped it a few times to no avail. Am I missing something?

=IF(NOT(ISBLANK([[email protected]])), "Complete", IF(AND(NOT(ISBLANK([Ad RSS Link]@row)), NOT(ISBLANK([Done]@row)), NOT(ISBLANK([Subject Line]@row)), "Proofing", IF(NOT(ISBLANK([Ads Entered]@row)), "Proofing", "Waiting for Information"))))

Thank you so much for the help and insight!

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF([email protected] = 1, "Complete", IF(OR([Ads Entered]@row = 1, AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", [email protected] <> "")), "Proofing", "Waiting For Information"))


    The two issues I see with your formula are:

    1. Your first cell reference has a misplaced square bracket after "@row" instead of after the column name.
    2. You have a closing parenthesis at the very end of the formula that needs to be moved to NOT(ISBLANK([Subject Line]@row)) so that it closes out the AND statement.

    thinkspi.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF([email protected] = 1, "Complete", IF(OR([Ads Entered]@row = 1, AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", [email protected] <> "")), "Proofing", "Waiting For Information"))


    The two issues I see with your formula are:

    1. Your first cell reference has a misplaced square bracket after "@row" instead of after the column name.
    2. You have a closing parenthesis at the very end of the formula that needs to be moved to NOT(ISBLANK([Subject Line]@row)) so that it closes out the AND statement.

    thinkspi.com

  • That worked perfectly. Thank you so much!

  • Alan P.
    Alan P.
    edited 05/17/22

    At first glance, if you're using a 'Check' field for [Approved], you first condition in your if statement should look for a 1 or 0:

    IF([Approved]@row = 1, "Complete".........

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Alan P. You can still use the NOT(ISBLANK(.......)) for a checkbox type column. It may require a little more typing, but it still works. You can also use true for checked and false for unchecked since it is boolean.

    thinkspi.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Megan Yaussi Happy to help. 👍️

    thinkspi.com

  • Hi @Paul Newcome

    Yeah I knew that logically, a NOT(IS BLANK will work, but I preferred to use the truncated logic of the boolean 1 and 0. Easier on the eyes and makes it a lot easier to explain if Megan wanted to share with another user onsite.

    All in all, an unanswered question is answered :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Alan P. I agree that using 1 vs 0 is much easier to read/manage.


    The way I read your comment though, it seemed (to me at least) that you were suggesting that was the actual problem with the formula and not necessarily a "personal preference" type of suggestion.

    thinkspi.com