Nested Is Blank Statement

I feel silly... for some reason if the first cell is populated, my formula cell turns blank. If the other cells are populated, the formula works as expected. Ultimately I want to look at 5 different cells, if they're blank, I want "Unassigned" returned. If any of them have text, I want "Assigned" returned. What am I doing wrong? :/


=IF(ISBLANK([Explore Financial - Assigned To]1), (IF(ISBLANK([Explore Operations - Assigned To]1), (IF(ISBLANK([Explore Market Updates - Assigned To]1), "Unassigned", "Assigned")))))

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 09/05/20

    Hi @Brent Harmon


    Try something like this. (This example assumes that your columns are next to each other)

    =IF(COUNTIF([Explore Financial - Assigned To]@row:[Explore Market Updates - Assigned To]@row; <>"") > 0; "Assigned"; "Unassigned")

    The same version but with the below changes for convenience.

    =IF(COUNTIF([Explore Financial - Assigned To]@row:[Explore Market Updates - Assigned To]@row, <>"") > 0, "Assigned", "Unassigned")

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.


    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Brent Harmon

    Your formula returns a blank cell, because your nested IF statements only have a TRUE statement, and no FALSE statement. This probably have to do with the parenthesis you included prior to each nested IF statement.

    @Andrée Starå's solution will work nicely if your cells are next to each other. If they aren't, you may try this instead:

    =IF(AND(ISBLANK([Explore Financial - Assigned To]@row), ISBLANK([Explore Operations - Assigned To]@row), ISBLANK([Explore Market Updates - Assigned To]@row), ISBLANK([Column #4]@row, ISBLANK([Column #5]@row)), "Unassigned", "Assigned")

    Just another way to do it ;)

    Hope it helped :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!