Formula for multi-cell status update

Options
Eric Slate
edited 12/09/19 in Formulas and Functions

Hi all - First Discussion here. I am trying to use a formula that will populate a cell with "Not Started", "In Progress" or "Completed" based on the condition of eight cells in the same row have data in them or not.I have created two formulas that work in part but cannot figure out how to properly combine them.The idea is to have the cell show "Not started" when all eight cells are blank, "In Progress" when any of the eight cells are not blank, and show "Completed" when all eight cells are not blank. Any Suggestions?

This formula shows "Completed" only when all eight cells have data, otherwise it shows "In Progress"

=IF(OR(ISBLANK(INST3), ISBLANK(ASGN3), ISBLANK(TEST3), ISBLANK(CODE3), ISBLANK(ALRM3), ISBLANK(GRPH3), ISBLANK(TREND3), ISBLANK(TUNE3)), "In Progress", "Completed")

This formula shows "In Progress" when any of the eight cells have data, or "Not Started" when all cells are blank

=IF(AND(ISBLANK(INST3), ISBLANK(ASGN3), ISBLANK(TEST3), ISBLANK(CODE3), ISBLANK(ALRM3), ISBLANK(GRPH3), ISBLANK(TREND3), ISBLANK(TUNE3)), "Not Started", "In Progress")

Capture.JPG

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Eric,

    Welcome to the community!

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • Eric Slate
    Options

    Hi Andree - Thank you for offering to help. I have 8 columns (INST-TUNE) that field personnel will be updating at different intervals and a "State" column that needs to update when data is added when entered into any or all of the previously mentioned 8 columns in that row. I would like the sheet to work like this:

    When all 8 columns in a row are empty, I want the "State" Column for that row to show "Not Started"

    When any of the 8 columns in a row have data (Date, initials, checkbox, etc), I want the "State" column for that row to show "In Progress"

    When all 8 columns in a row have data, I want the "State" column for that row to show "Complete"

    Hope that makes sense? I can try to clarify more if needed. I added you to the sheet as well. Thank you again!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Happy to help!

    Best,

    Andrée

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!