Automatically updating Status column based on presence of data in other cells

Options

I'm trying to have my Status column update automatically based on other columns being empty or not empty. For example:

IF Column A = empty, THEN Status = "New"

IF Column A = not empty, THEN Status = "In Progress"

IF Column B = not empty, THEN Status = "Awaiting Approval"

IF Column C = not empty, THEN Status = "Done"


In my workflow, Columns A-C will each be filled out by a different user, and will always be filled in in that order (A, then B, then C). Therefore, I don't need to worry about IF Column A AND Column B = not empty. I can just know that if data is present in X column, display Y status.


I'm assuming I can do this by using an IF statement that has the logical expressions in the correct order (i.e., check Column C first, then Column B, then Column A).


For some background, I'm working around the fact that SS doesn't allow me to set a value in my Status column based on the submission of a form/ update request/ approval. Also, using a formula isn't ideal, because I would like for users to be able manually update the Status column (if I use the IF formula, and then a user manually overrides it, it will prevent the Status from updating automatically in the future).


Is there a better way I can be doing this? Or if not, does the strategy I laid out make sense/ have any flaws?


Thank you in advance for any and all help!

Best Answer

Answers

  • sschreiner
    Options

    Thanks @Kelly Drake, I tried your formula (adapted for my column names) and I'm getting an unparseable error. It's probably an error I injected when adapting it, but I can't seem to find the issue:


    =IF(NOT(ISBLANK([Additional Eval Question #1]1)), “Completed”, IF(NOT(ISBLANK([Worker Eval Question #1]1)), “In progress”, “Awaiting worker response“))


    p.s. I agree with what you said about the limitations with using the hidden field on the form. It was a good idea though, I might use it in the future for other workflows.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @sschreiner

    Just to jump in here - you adjusted the formula correctly but I can see that your quotation marks are curved, like so: ” instead of straight as they appear in Smartsheet, like so: "

    It's one small detail, but copying over certain types of quotation marks will cause formulas to error, as they're recognized as different symbols. Try the exact same formula, but re-type out the quotes directly into Smartsheet so it's the right symbol. 🙂

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    Hi @sschreiner

    Depending on your language version you may have to replace , with ; and/or " with '.


    Hope it helped.

  • sschreiner
    Options

    Thanks Genevieve, my tired eyes didn't notice that! That did the trick :)

  • Kelly Drake
    Kelly Drake Overachievers Alumni
    Options

    Ohe other edit I would suggest - ... Change the cell reference to @row... it helps optimize your formula/sheet.

    https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!