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

sschreiner

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!

Kelly Drake

You might need to rework the order in your data but I think this should work

=IF(NOT(ISBLANK([Column B]@row)), "Done", IF(NOT(ISBLANK([Column B]@row)), "Awaiting Approval", IF(NOT(ISBLANK([Column A]@row)), "In Progress", "New")))

If you're using a form though you could add your Status Column into the form as a hidden cell that populates with a default value - I assume that would be "New". But I haven't used this for a field that would subsequently be updated via formula. This approach may require someone to copy/paste the formula for all new rows.

As for avoiding formula overrides - my best practice is to lock down columns that run automations like this so that only people with Admin permissions can override.

Kelly Drake

You might need to rework the order in your data but I think this should work

=IF(NOT(ISBLANK([Column B]@row)), "Done", IF(NOT(ISBLANK([Column B]@row)), "Awaiting Approval", IF(NOT(ISBLANK([Column A]@row)), "In Progress", "New")))

If you're using a form though you could add your Status Column into the form as a hidden cell that populates with a default value - I assume that would be "New". But I haven't used this for a field that would subsequently be updated via formula. This approach may require someone to copy/paste the formula for all new rows.

As for avoiding formula overrides - my best practice is to lock down columns that run automations like this so that only people with Admin permissions can override.

sschreiner

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.

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

Hi @sschreiner

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

Hope it helped.

sschreiner

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

Kelly Drake

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